Sort Aborted on a indexed query

I can’t figure out why this query is sometimes throwing a sort aborted error when it shouldn’t be sorting anything since its’ fully indexed.

This is my query:

SELECT * FROM comments WHERE (comments.commentable_id = 19751619 and comments.commentable_type = ‘User’) ORDER BY comments.id desc LIMIT 20, 20;

This is the explain:

±—±------------±---------±-----±-----------------------------------------------------------------------------------------±------------------------------------------------------±--------±------------±------±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±---------±-----±-----------------------------------------------------------------------------------------±------------------------------------------------------±--------±------------±------±------------+| 1 | SIMPLE | comments | ref | index_comments_on_commentable_id_and_commentable_type,index_comments_on_commentable_type | index_comments_on_commentable_id_and_commentable_type | 263 | const,const | 14032 | Using where | ±—±------------±---------±-----±-----------------------------------------------------------------------------------------±------------------------------------------------------±--------±------------±------±------------+

This is the table stats:

±---------±-------±--------±-----------±---------±---------------±------------±----------------±-------------±----------±---------------±--------------------±------------±-----------±------------------±---------±---------------±---------------------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |±---------±-------±--------±-----------±---------±---------------±------------±----------------±-------------±----------±---------------±--------------------±------------±-----------±------------------±---------±---------------±---------------------+| comments | InnoDB | 10 | Compact | 46686217 | 292 | 13670285312 | 0 | 3648552960 | 0 | 102228668 | 2008-04-28 17:23:16 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 8192 kB | ±---------±-------±--------±-----------±---------±---------------±------------±----------------±-------------±----------±---------------±--------------------±------------±-----------±------------------±---------±---------------±---------------------+

The index it is using is (commentable_id, commentable_type) and since it’s innodb, followed by id so: (commentable_id, commentable_type, id).

We only get the sort aborted error when there are over 10k comments for that user, but nonetheless, it’s indexed.

Does the engine need to return all the rows to the server even when there is a limit? Does MySQL have to reverse the rows in order to do a order by desc on a ascending index? What can I do to improve this query?

Any help would be greatly appreciated. Thank you.

I’m not sure, but I guess limit 20,20 is the bad guy.

What if you just simply limit 20 and limit 40?

Sometimes its worth more, to drop the first 20 line from php (or whatever you got) and not form mysql.

Regards,
Istvan