Super Slow Query & Super high CPU

[paulman888888]

Hello again,

 

I got a problem with one of my pages that uses MySql. I didn't expect my site to shoot up into the 1000's per hour but it has but has destroyed the loading time of one of my pages.

 

Below is the function that is super slow!

function get_top($timeframe='today', $time=10, $friends=true, $limit=5, $unique=true, $me_only=false){
if($friends===false){$q='';}else{
	$q=' AND (username IN ('; // This array can have upto 500 items in it.
global $friend_id_array, $me;$tarray=$friend_id_array; $tarray[]=$me['id'];
	$x= implode(',',$tarray);$q.=$x;
	$q.= '))';
}
if($me_only){
	$q=' AND username = \''.$me['id'].'\' ';
}

		if($timeframe=='today'){
		$q_timeframe='AND DATE(thedate) = DATE(NOW()) AND YEAR(thedate) = YEAR(NOW())'; // Checking the date could be slow
	}else
	if($timeframe=='week'){
		$q_timeframe='AND WEEK(thedate) = WEEK(NOW()) AND YEAR(thedate) = YEAR(NOW())';
	}else
	if($timeframe=='month'){
		$q_timeframe='AND MONTH(thedate) = MONTH(NOW()) AND YEAR(thedate) = YEAR(NOW())';
	}else
	if($timeframe=='all'){
		$q_timeframe='';
	}
if($unique){$q_unique='GROUP BY username';}
$query="SELECT MAX(score) as highscore, username, timeframe, thedate FROM other_click WHERE (timeframe = '$time') $q $q_timeframe $q_unique ORDER BY highscore  DESC LIMIT 0, $limit"; // A very long query
$result=@mysql_query($query)or die(mysql_error());
if(@mysql_num_rows($result)>0){
while($row=@mysql_fetch_array($result)){ // Maybe resorting the array makes it super slow
	$id_array[]=$row;
}
return $id_array;}else{return false;}
}

 

I've commented the lines which I think maybe making this page load slowly.

 

I also thought the way the page is loaded.

The page is called 6 times per user but with different settings eg, Friends only and only scores added today.

 

Please help because this is crippling my site.

 

Thank-you all

Paul

[DavidAM]

1) Is the 'username' field actually the ID field and is it numeric? Is this field indexed?

2) I'm not sure that sending 500 ID's in an IN phrase is a good idea. Are these in a database table as well? Because a JOIN might actually perform better.

3) Don't copy the array and then implode it, just use it:

		$x= implode(',',$friend_id_array);
	$q.=$x . ',' . $me['id'];
	$q.= '))';

of course, you'll want to be sure to handle the case where it might be empty

 

4) I think you want DAY(thedate) instead of DATE(thedate). DATE() returns YEAR-MONTH-DAY so there is no sense in testing the YEAR(thedate) after that. DAY() is a synonym for DAYOFMONTH() which returns a value between 1 and 31.

 

5) Having said that, using the functions in the WHERE clause will prevent any index use on 'thedate' and will use more resources.  You might be better off calculating the date range and using it in the query; like for instance:

 AND thedate BETWEEN '2010-08-08' AND '2010-08-14'
// IF thedate IS A DATETIME FIELD USE
AND thedate BETWEEN '2010-08-08 0:00:00' AND '2010-08-14 23:59:59'

and possibly adding an index on 'thedate'

 

6) I don't see that you are "resorting" the array. You are retrieving the data from the database, which you have to do.

 

You'll need to look at the query plan (see EXPLAIN) to see where the query is getting bogged down. Or post the results here for review.  You may need additional indexes on the table.  You'll want to look at the plan for several or all of the 6 different calls you make to this function.

 

Also, you might want to profile the code to see if this function is in fact causing the bottleneck.

 

Note: A query can only use, at most, 1 (one) index per table. You have to look at the ways that you query the data and choose the column(s) that get you closest to the final data.

 

One last point. You know your application and database (well, at least more than I do). Consider the possibility of making a single call to the database to get all of the data you need for the 6 calls you make now.  Add some flags to the data that is returned to indicate where it is needed. Then sort it out to the six places on the front end. It will add more front-end (PHP) processing; but might provide an overall performance improvement. Or at least consolidate some combination of calls. It might not be possible, but it's worth a thought.

 

[gizmola]

I want to 2nd DavidAM's advice.  One thing I would add is that the calculations you are doing in your SQL like

 

AND WEEK(thedate) = WEEK(NOW()) AND YEAR(thedate) = YEAR(NOW())

 

Mean that mysql can not use an index on thedate.  You want to change this so that you arrive at a date range and you can do specific "between" queries as he suggested.  There's no reason why you can't do those sorts of computations.  I have a couple of articles on mysql datatime functions on my blog that might help spark ideas of how to rewrite your code.

 

 

http://www.gizmola.com/blog/archives/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

http://www.gizmola.com/blog/archives/99-Finding-Next-Monday-using-MySQL-Dates.html