StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStrange MySQL query performance
text
Body
copied!<p>[Solved: Look at edit 2] The following query takes 37 seconds to finish (This query gets the 50th post a user has made and the corresponding creation date)</p> <pre><code>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 </code></pre> <p>whereas the following takes 30 minutes to finish (5th post)</p> <pre><code>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 </code></pre> <p>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 <code>limit 49,</code> 1 vs <code>limit 4, 1</code> </p> <p>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?</p> <hr> <p>Explain output:</p> <pre><code>--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) </code></pre> <p>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(creationdate, owneruserid) and there's no visible difference in performance.</p> <p>Edit2: Finally got it working by using <code>force index (idx_owneruserid)</code> on the 1st subquery. Lesson learnt: When explain plan doesn't use your indices as expected, use force index.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload