Speed up query code

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
michelem
Forum Newbie
Posts: 1
Joined: Fri Feb 01, 2008 6:54 am

Speed up query code

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Speed up query code

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply