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:

enter image description here

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

enter image description here

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.

I believe sub selects are iterated repeatedly for every record in the outer select, which is why is seems so high. You could probably restructure the query to avoid the sub selected — Vinbot, Apr 15 '15 at 15:59
possible duplicate of [How to improve Limit clause in MySQL](http://stackoverflow.com/questions/29649131/how-to-improve-limit-clause-in-mysql) — Siyual, Apr 15 '15 at 16:03
@Vinbot actually I dont know how I can achive the same result with pegination using the different query structure. Could you give me some example? — Speise, Apr 15 '15 at 17:33
@Siyual Here is no the duplication bacause the origin question on other branch is about simple Limit query and there is the right answer. All other communication was about more complex query so it is better to sepereate the questions to the different branches. — Speise, Apr 15 '15 at 17:34
@Speise On the plus side, it's using index so it doesn't have to go to the database files to fetch the values, it's pulling it from the index which is pretty fast. Load up your posts table with 1,000,000 records and see how fast it is. — Vinbot, Apr 15 '15 at 18:10
Another option would be to just run two separate queries. First, get the list of post_ids using your limit query. Then, dynamically build the second query to filter by post_id using IN like this: `select * from comments where post_id in (3,5,19,105);` — Vinbot, Apr 15 '15 at 19:57

score 0 · Answer 1 · answered Apr 16 '15 at 20:03

0

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

answered Apr 16 '15 at 20:03

symcbean

47,736
6
59
94

However there is still a question - why does MySQL estimate subquery differ from a the same separate query? – Speise Apr 17 '15 at 12:42
Because the sub query needs to return an ordered set to efficient lookup he the other table – symcbean Apr 18 '15 at 21:19

score 0 · Answer 2 · answered Apr 18 '15 at 07:44

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.

How to improve LIMIT clause in MySQL subquery?

2 Answers2

Linked