The following query takes 37 seconds to finish (This query gets the 50th post a user has made and the corresponding creation date)
SELECT p.id,
(SELECT id
FROM posts
WHERE owneruserid = p.id
ORDER BY creationdate
LIMIT 49, 1) AS post50id,
(SELECT creationdate
FROM posts
WHERE id = post50id)
FROM prol_users p
WHERE postcount >= 50
whereas the following takes 30 minutes to finish (5th post)
SELECT p.id,
(SELECT id
FROM posts
WHERE owneruserid = p.id
ORDER BY creationdate
LIMIT 4, 1) AS post5id,
(SELECT creationdate
FROM posts
WHERE id = post5id)
FROM prol_users p
WHERE postcount >= 50
Please notice that it is the first time I'm running the queries, so there's no caching involved. The only difference between the first query and 2nd is limit 49, 1 vs limit 4, 1
Is there any reason why it takes lesser time when the query is limited to 50 rows than when it is limited to 5 rows?
Explain output:
--Note: The faster one, limit 50
mysql> explain select p.id, (select id from posts where owneruserid = p.id order by creationdate limit 49,1) as post50id, (select creationdate from posts where id = post50id) from prol_users p where postcount >= 50;
+----+--------------------+-------+--------+--------------------------+-----------------+---------+------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+--------------------------+-----------------+---------+------------+--------+-----------------------------+
| 1 | PRIMARY | p | ALL | NULL | NULL | NULL | NULL | 199026 | Using where |
| 3 | DEPENDENT SUBQUERY | posts | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | posts | ref | idx_owneruserid,idx_ouid | idx_owneruserid | 5 | jagat.p.id | 11 | Using where; Using filesort |
+----+--------------------+-------+--------+--------------------------+-----------------+---------+------------+--------+-----------------------------+
3 rows in set (0.00 sec)
--Note: The slower one, limit 5
mysql> explain select p.id, (select id from posts where owneruserid = p.id order by creationdate limit 4,1) as post5id, (select creationdate from posts where id = post5id) from prol_users p where postcount >= 50;
+----+--------------------+-------+--------+--------------------------+------------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+--------------------------+------------------+---------+------+--------+-------------+
| 1 | PRIMARY | p | ALL | NULL | NULL | NULL | NULL | 199026 | Using where |
| 3 | DEPENDENT SUBQUERY | posts | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | posts | index | idx_owneruserid,idx_ouid | idx_creationdate | 8 | NULL | 5 | Using where |
+----+--------------------+-------+--------+--------------------------+------------------+---------+------+--------+-------------+
3 rows in set (0.00 sec)
Edit: I tested with various limit values and noticed that performance improves drastically when limit is changed from 9,1 to 10,1. In fact, the explain plan changes as well (to that of 50). Any insight on why it does so? Also, I added an index posts(owneruserid, creationdate) and there's no visible difference in performance.
Note: I have posted this on StackOverflow and had a long discussion with some SO users, but it was of no avail. I'm posting here, hoping someone more acquainted with MySQL tuning would throw some light on it.
Please have a look at the post here
http://stackoverflow.com/questions/15555862/strange-mysql-query-performance
and the discussion here
http://chat.stackoverflow.com/rooms/26670/discussion-between-cdhowie-and-jagat