GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Sort Aborted on a indexed query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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_t ype,index_comments_on_commentable_type | index_comments_on_commentable_id_and_commentable_t ype | 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.

  • #2
    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

    Comment

    Working...
    X