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
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
