List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 16 2009 8:34pm
Subject:Re: Slow query Performance
View as plain text  
In the last episode (Jul 15), Tachu(R) said:
> I'm having random query slowness that i can only reproduce once. My main
> question is that the query runs faster the second time around but i dont
> have query cache enabled here is some info from mysql profiler;
> 
> The time is spent mostly on the sending data step
> first time around
> 
> 63 rows in set (0.51 sec)
> 
> Second time around
> 
> 63 rows in set (0.00 sec)
> 
> Any ideas how i can improve the performance of the query. the explain
> 
> explain select user_id,result_id from score where quiz_id='495536' and
> user_id in (594939703, 641833475, 648583496, 663932271, 791002140,
> 844089643, 1014189359, 1014996058, 1021011357, 1035297313, 1043753292,
> 1103136802, 1107070131, 1114628916, 1129457032, 1133091309, 1188705251,
> 1211995704, 1219452575, 1219631303, 1239604246, 1241474238, 1266412488,
> 1266549868, 1288719892, 1289732597, 1317205736, 1346089661, 1350738033,
> 1354967647, 1356046070, 1369391720, 1374076904, 1406156780, 1407302487,
> 1414151928, 1425275210, 1457839666, 1466635900, 1484315366, 1493410149,
> 1536834812, 1544094394, 1572354290, 1575139632, 1578136049, 1587129534,
> 1592996678, 1594617334, 1615538051, 1615906710, 1621733854, 1622940529,
> 1646693120, 1674002418, 1684166314, 1684535294, 1701864533, 1703227082,
> 1711823847, 1713353427, 1732903860, 1752595138, 1758240924, 1813245914,
> 1815724221, 1839942291, 100000015406640);
>
>
> +----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
> | id | select_type | table | type  | possible_keys   | key     | key_len | ref  |
> rows | Extra       |
>
> +----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
> |  1 | SIMPLE      | score | range | user_id,quiz_id | user_id | 12      | NULL |  
> 68 | Using where |
>
> +----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
> 1 row in set (0.02 sec)
> 
>   UNIQUE KEY `user_id` (`user_id`, `quiz_id`), 

Try swapping those fields in the compound index.  The way you have it, mysql
has to jump to each of the 68 user_id values in the index and see if one of
the quiz_ids is 495536.  If you have an index on (quiz_id,user_id), mysql
only has to jump to the 495536 quiz_id section, and all the user_ids are all
right there.  

That should cut your query time by 50% (since you still have to do 68 seeks
to the table rows to fetch result_id).  If you also add result_id to your
compound index, then mysql will be able to get all its information from the
index without having to go to the table at all.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Slow query PerformanceTachu®16 Jul
  • Re: Slow query PerformanceDarryle Steplight16 Jul
    • Re: Slow query PerformanceDon Read16 Jul
      • RE: Slow query PerformanceMartin Gainty16 Jul
  • Re: Slow query PerformanceDan Nelson16 Jul