Simple MySQL query is blocking when adding ORDER BY with LIMIT

Question

I have a table tb_profilecomments:

4,3 GiB -- 8 Million rows total -- InnoDB

with 3 indexes:

The query I run is simple:

SELECT *
FROM tb_profilecomments
WHERE profilecomment_user_id=6430
ORDER BY profilecomment_id DESC

Getting a result in less than 1 second (16.000+ rows). When I now add LIMIT 1, 5 to the query then I got to wait more than 2 minutes to get a result.

Don't know what happens in the mysql background why it's slowing down the query so heavily.

When I remove ORDER BY or LIMIT from the query, then everything is fine.

When sorting it by non-indexed profilecomment_date column, then it's slow (7 seconds), but not 2 minutes like when sorting and limiting with the indexed primary key profilecomment_id.

Do you have any idea what's wrong? A broken index maybe? How to find out? How to fix? ANALYZE TABLE says message "ok".

EXPLAIN tb_profilecomments:

SHOW CREATE TABLE tb_profilecomments:

Result EXPLAIN SELECT * FROM tb_profilecomments WHERE profilecomment_user_id=6430 ORDER BY profilecomment_id DESC LIMIT 1, 5:

Answer 1

The rows within an index are very definitely ordered. ASC only. Even the 5.7 manual page says

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

MySQL has always had that limitation. (Upgrading won't help.)

But... That does not stop the code from delivering results in DESC order.

That is, MySQL's deficiency of not having DESC indexes rarely matters. Here is one case where it does matter: ORDER BY x ASC, y DESC. No INDEX can be built (in MySQL) to efficient handle that.

The optimal index for your query is

INDEX(user_id, comment_id);

user_id will be used for WHERE user_id = _constant_, then comment_id will be scanned in reverse order to get the rows you desire. With or without a LIMIT.

But... The index must handle all of WHERE, GROUP BY, and ORDER BY before LIMIT short-circuits execution. (Otherwise, there is a filesort, tmp table, etc, before LIMIT can be applied.)

For the extra-slow queries, please provide EXPLAIN SELECT ... and (if possible), EXPLAIN FORMAT=JSON SELECT ....

Do not drop the PRIMARY KEY on an InnoDB table! It is vitally needed. (OK, drop it if you are replacing it.)