Mysql is not using index for sort in composite index

Question

I have a big table (around 70milion records) and I want to get some result in a order from database.

Here is my query

SELECT * 
FROM `comment` 
WHERE `account_id` IN ('accountId1','accountId2') 
ORDER BY `date` desc, `id` desc 
LIMIT 20; 

I'm trying to get comments for specific account and I need to order them by date and if they have same date I need to order them by id desc.

I added index to improve the query like this:

ALTER TABLE `comment` 
ADD INDEX `account_id` (`account_id`, `date`, `id`); 

My problem is it used filesort for sorting result and it's not using index.

Here is the explain query

+----+-------------+--------------------+------------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table              | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                 |
+----+-------------+--------------------+------------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | np_account_comment | NULL       | range | account_id    | account_id | 5       | NULL | 20757 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------------------+------------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------+

In the explain it said key_len is 4 so its only use first part of index (account_id) as index and its not using date and id for sorting.

Here is what I understood

1. Its not depend on order by desc, I tested on order by asc and it has same result.
2. The problem probably is because of account_id IN ('accountId1','accountId2') because when I using ``account_id = 'accountId1' I have these result

+----+-------------+--------------------+------------+------+----------------------+------------+---------+-------------+------+----------+---------------------+
| id | select_type | table              | partitions | type | possible_keys        | key        | key_len | ref         | rows | filtered | Extra               |
+----+-------------+--------------------+------------+------+----------------------+------------+---------+-------------+------+----------+---------------------+
|  1 | SIMPLE      | comment            | NULL       | ref  | account_id           | account_id | 4       | const,const |    1 |   100.00 | Backward index scan |
+----+-------------+--------------------+------------+------+----------------------+------------+---------+-------------+------+----------+---------------------+

Still key_len is 5 but in extra it doesnt said filesort

How I can improve my query speed? sometime it take 20sec to complete.

If I'm current when mysql used full index the key_len should be 13 ( 4(INT) * 2 + 5 (DATETIME) )

Answer 1

MySQL cannot use the index to get rid of the ordering, because after e.g. a result row accountId1, Jan 10th, the next result can be either accountId1, Jan 9th or accountId2, Jan 10th. So MySQL has to first check "all other accounts" (which requires sorting) before knowing what comes next.

There are (at least) 2 things that you can try:

First, you can reduce the rows read per account_id by reading them separately:

select * from
(
  (
   select * from `comment` 
   where `account_id` = 'accountId1' 
   order by `date` desc, `id` desc 
   limit 20
  )
  union all
  (
   select * from `comment` 
   where `account_id` = 'accountId2' 
   order by `date` desc, `id` desc 
   limit 20
  )
) sub
order by `date` desc, `id` desc 
limit 20; 

This reads 20 rows per account_id, and then sorts just those n * 20 rows. So you skip reading and sorting potentially 100k rows. This logic is basically how MySQL executes your originally query - but without the internal limit (because it isn't optimized for it).

The second thing you can try is to add a covering index. Currently, MySQL uses your index to find a row, and then reads the row from the table to fulfill the select *, which is an additional step. For that, you can add an index (account_id, `date`, id, all other columns in your table). And if you do not actually need all columns in your result, adjust the select * to only select the columns you need (this reduces the size of the index).

To test the effect of this new index before actually adding it, you can e.g. change the select * to SELECT `date`, id FROM `comment` WHERE ... (both for your query and the rewritten code above). This is covered by your current index, and should give you a rough idea how much time the new index can save if it covers select *. It can happen that it is neglectable (especially if you use the query from the first suggestion).

In the execution plan, this should be reflected by showing using where instead of using index condition.

You can try both things independently from each other.