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.