Page 1 of 1

Speed up query code

Posted: Fri Feb 01, 2008 7:00 am
by michelem
~pickle | Please use [ php ] tags when posting PHP code. Your post has been updated.

Hi,
how could I speed up the following code:

Code: Select all

 
for ($i=0;$i<=23;$i++) {
    $hour_fromdate = strtotime("+ ".$i." ".$strto, $this->from_date);
    $hour_todate = strtotime("+ 1 ".$strto, $hour_fromdate);
    $ghits = $wpdb->get_var("SELECT COUNT(*) FROM (SELECT DISTINCT column FROM $tableName WHERE column IS NOT NULL AND timestamp BETWEEN $hour_fromdate AND $hour_todate) AS itemstot");
    $ahits[ ] = $ghits;
}
 
The result's code is an array ($ahits) of how many hits there is in each hour of the day.
Could I exclude the for cycle and use only a mysql query to retrieve that array?
Thank you

~pickle | Please use [ php ] tags when posting PHP code. Your post has been updated.

Re: Speed up query code

Posted: Fri Feb 01, 2008 9:51 am
by pickle
What about something like this:
[sql]SELECT    count(*)FROM    $tableNameWHERE    COLUMN IS NOT NULL AND    timestamp BETWEEN $beginning_of_day_stamp AND $end_of_day_stampGROUP BY    DATE_FORMAT('%H',FROM_UNIXTIME(timestamp));[/sql]

That's untested & will likely give you 1001 errors. If something like that doesn't work, I'd change the query to just get all results from the desired day, then loop through the result set. It'll really cut down on the number of queries.