How to improve LIMIT clause in MySQL subquery?

Question

I have two tables : posts with 10k rows and comments and I need to select all comments for particular numbers of posts in other words implement the pagination by posts table and get all comments thereof. For that purpose I have the next query:

select * from  comments c 
inner join (select post_id from posts o order by post_id  limit 0, 10) p 
on c.post_id = p.post_id;

Also it is very important for me the performance of query. But the Explain of this query is very strange because LIMIT clause iterate through 9976 rows but not through 10 rows as I expect:

At the same time when I run subquery separately it works great with iterating through 10 rows as expected:

explain select post_id from posts o order by post_id  limit 0, 10

Also there is indexes on posts(post_id), comments(comment_id), comments(post_id). I don't understand what is the problem with that query so it iterate through all records in posts table. I will be very thankful if somebody help me with that issue.

Answer 1

Firstly, your qwuery did not iterate over 9976 rows. Explain shows an estimate of the number of rows the query will read (actually, it generates lots of execution plans and discards all but the one with the lowest cost estimate).

For limit 0,10 it may read much fewer rows (depending on how the indexes are configured) but when asked to resolve limit 10000, 10 it will read a lot more

Answer 2

9976 (vs 10000) is already an improvement -- before 5.6, "Rows" was often off by as much as a factor of 2. Now the statistics are more accurate, and more stable.

The real answer is "EXPLAIN is less than perfect."

5.7 will have some improvements. Meanwhile, we are stuck with mysteries like "10 vs 9976".

It is mostly broken when LIMIT is used. It manifests in another way in the "Filtered" column of EXPLAIN EXTENDED.

Try out EXPLAIN FORMAT=JSON ... to get a little more information.

With MariaDB (version 10.0?), there is ANALYZE SELECT ... which will give you actual counts. It does this by running the query, then tossing the resultset and keeping the statistics.