Need suggestions about efficent counter given 900 pecices of data.

Hi, so my problem is the algorithm in which I have takes about 80 seconds to load. I was wondering if anyone could guide me towards a faster algorithm, it is pretty basic.

There is 900 pieces of data which are in countries. So in order to track the amount of users coming from one country, I put an if statement:

if ($country =="United States")
{
$UnitedStates = $UnitedStates +1
}

and soo on for the other countries. My problem is this is inefficient as 900 pieces of data go slowly through this process which finishes loading in 80 seconds. Can anyone suggest a faster method in which all the countries can be recorded in the counter for the 900 pieces of data to load under 10 seconds?

Thank you.

 

Three possibilities. First the neatest one:

$count = array();
//loop
if(isset($count[$country]))++$count[$country];
else $count[$country]++;
//End loop

PHP:

Second, the shortest, and probably the fastest one

$count=array()
//loop
@++$count[$country];
//End Loop

PHP:

And if you really don't want to use arrays:

//Loop
++${$country};
//End loop

PHP:

although I'm not sure if it works in the case of United States, with the space inbetween.

 

Thank you it works well. I'm using the second one. Instead of using full country name, I am now using 3 letter country codes. Thanks for the help.

For 900 items going through the system in terms of time has improved from 80 seconds to 20 seconds. Which is amazing, I guess the other 20 seconds is in the mysql query when it goes through the while loop. How can I increase the speed in which this process is done so it loads in less than 5 seconds which is a regular load for a page?

 

You could do the whole thing in one SQL statement and get an array of records that have the count of each country. It may be faster or it may not. You'd have to try it to find out.

 

What do you mean by one statement?

This is the most important part of the code which I have.

 $query = mysql_query("SELECT ip-addresses FROM table WHERE video='2'");
			while($row = mysql_fetch_array($query)){
$theip = $row['pollip_ip'];
$country = PepakIpToCountry::IP_to_Country_XXX($theip);
@++$count[$country];  
}

PHP:

 

What Rukbat suggested is the optimum way of doing this. I recommend you go with that.

 

Don't just save the IP address in the database. At the time you're saving it, look up the country and store that too. Then you just select the counts of the countries, grouped by country.

 

The problem is I have over 29,000 data. This is just one set of data where the id is 2. If I add another column for country and optimize the current script I have for coding and converting the 29k into countries which will be stored in the database will take a very long time.

I just need an efficient need an efficient way of what I am currently doing.

Is there a default towards the memory usage on how fast the queries are processed in mysql?

 

apply indexes on db table for fast execution.