MySQL uses incorrect index. Why?

Question

There is a typical Users table, which contains fields: id (primary), application_id, login, phone, etc. (application_id - selective field)

There are few indexes:

index_users_on_application_id,
unique_index_users_on_application_id_and_login
unique_index_users_on_application_id_and_phone

The query itself is very simple:

SELECT  `users`.*
FROM `users`
WHERE `users`.`application_id` = 1234
LIMIT 10 OFFSET 0;

The tricky part is that this query uses one of two unique indexes (unique_index_users_on_application_id_and_login for example), and then returns list of users sorted by login. But I need them sorted by id.

For that purpose, I've updated the query:

SELECT  `users`.*
FROM `users`
WHERE `users`.`application_id` = 1234
ORDER BY id
LIMIT 10 OFFSET 0;

Well, now explain shows that MySQL starts using PRIMARY key instead of any indexes. But how did that happen? If index_users_on_application_id should in fact contain two fields: [application_id, id] (InnoDB), so that index is perfect for the query, but MySQL decides to chose another one.

If I say IGNORE INDEX(PRIMARY), MySQL starts using unique_index_users_on_application_id_and_login, still ignoring the correct index. Same result when ORDER BY id+0.

I also tried to ORDER BY application_id, id to make sure index fits the best, MySQL still selects wrong index.

Any ideas, why is it happening and how to ensure MySQL to use proper index without explicitly say USE INDEX(index_users_on_application_id)?

Full list of indexes for Users table:

mysql> show indexes from users;
+-------+------------+-----------------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                                            | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY                                             |            1 | id                   | A         |       21893 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | index_users_on_confirmation_token                   |            1 | confirmation_token   | A         |          28 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_reset_password_token                 |            1 | reset_password_token | A         |          50 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_external_user_id  |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_external_user_id  |            2 | external_user_id     | A         |         995 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_login             |            1 | application_id       | A         |          30 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_login             |            2 | login                | A         |       21893 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | users_account_id_fk                                 |            1 | account_id           | A         |          44 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | users_blob_id_fk                                    |            1 | blob_id              | A         |         118 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_remember_token                       |            1 | remember_token       | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id                       |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_facebook_id       |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_facebook_id       |            2 | facebook_id          | A         |        3127 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_twitter_digits_id |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_twitter_digits_id |            2 | twitter_digits_id    | A         |         138 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_email             |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_email             |            2 | email                | A         |        2189 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_full_name         |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_full_name         |            2 | full_name            | A         |        5473 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 rows in set (0.01 sec)

Example of EXPLAIN:

mysql> EXPLAIN SELECT  `users`.* FROM `users` WHERE `users`.`application_id` = 56374  ORDER BY id asc LIMIT 1 OFFSET 0;
+----+-------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys                                                                                                                                                                                                                                                                                                  | key                                                | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | users | ref  | index_users_on_application_id_and_external_user_id,index_users_on_application_id_and_login,index_users_on_application_id,index_users_on_application_id_and_facebook_id,index_users_on_application_id_and_twitter_digits_id,index_users_on_application_id_and_email,index_users_on_application_id_and_full_name | index_users_on_application_id_and_external_user_id | 5       | const |    1 | Using where; Using filesort |
+----+-------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

The problem itself is that using wrong index causes queries like that (with a limit of 100 instead of 1) to be performed MINUTES, while with a correct index it is a matter of split second.

Profiling:

SET PROFILING = 1; SELECT `users`.* FROM `users` WHERE `users`.`application_id` = 56374  ORDER BY id asc LIMIT 1 OFFSET 0; SHOW PROFILE FOR QUERY 1; SET PROFILING = 0;
Query OK, 0 rows affected (0.00 sec)

+----------+----------+-----------------+-------+-------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+-------+---------+---------------------+---------------------+---------------------+------------------+-------------+------------+---------+----------------------+------------------------+---------------------+--------------------+---------------------+----------------------+-------------------+------------+----------------+-------------+---------------------------------------------------------------------------------------+-------------------+-------------------------+--------------------------------------------------+----------------+
-- fields list --
+----------+----------+-----------------+-------+-------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+-------+---------+---------------------+---------------------+---------------------+------------------+-------------+------------+---------+----------------------+------------------------+---------------------+--------------------+---------------------+----------------------+-------------------+------------+----------------+-------------+---------------------------------------------------------------------------------------+-------------------+-------------------------+--------------------------------------------------+----------------+
| 27265241 |     NULL | Some Username | NULL  | 9777  | SomeHash | AnotherHash | NULL  | NULL    | 2017-04-12 15:53:32 | 2017-09-21 13:39:51 | 2017-09-24 19:19:06 |             1234 | NULL        | NULL       |    NULL | NULL                 | NULL                   | NULL                | NULL               | 2017-07-05 10:59:59 | NULL                 | NULL              |      12345 | NULL           | NULL        | something_else | NULL              |                       1 | another_hash |          54321 |
+----------+----------+-----------------+-------+-------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+-------+---------+---------------------+---------------------+---------------------+------------------+-------------+------------+---------+----------------------+------------------------+---------------------+--------------------+---------------------+----------------------+-------------------+------------+----------------+-------------+---------------------------------------------------------------------------------------+-------------------+-------------------------+--------------------------------------------------+----------------+
1 row in set (1 min 14.43 sec)

+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000068 |
| Waiting for query cache lock   |  0.000025 |
| init                           |  0.000025 |
| checking query cache for query |  0.000047 |
| checking permissions           |  0.000026 |
| Opening tables                 |  0.000031 |
| After opening tables           |  0.000025 |
| System lock                    |  0.000025 |
| Table lock                     |  0.000026 |
| Waiting for query cache lock   |  0.000037 |
| init                           |  0.000046 |
| optimizing                     |  0.000032 |
| statistics                     |  0.000225 |
| preparing                      |  0.000042 |
| executing                      |  0.000025 |
| Sorting result                 |  0.000057 |
| Sending data                   | 42.952100 |
| end                            |  0.000070 |
| query end                      |  0.000027 |
| closing tables                 |  0.000025 |
| Unlocking tables               |  0.000028 |
| freeing items                  |  0.000028 |
| updating status                |  0.000039 |
| cleaning up                    |  0.000025 |
+--------------------------------+-----------+
24 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

user_6059896 · Answer

You should be able to use index-hints and optimizer hints to suggest correct index usage:

Index hints

Optimizer hints

You could hint directly the table:

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

In your case I think the best solution would be to name the attributes by name and not to use star * and to use IGNORE INDEX (unique_index_users_on_application_id_and_login, unique_index_users_on_application_id_and_phone ) FOR ORDER BY directly at the query:

An example based on your code:

SELECT  u.id,
        u.application_id,
        u.login,
        u.phone,
        # ... here to continue
FROM users as u
IGNORE INDEX (unique_index_users_on_application_id_and_login, unique_index_users_on_application_id_and_phone ) FOR ORDER BY
WHERE u.application_id = 1234
ORDER BY u.id
LIMIT 10 OFFSET 0;

First Edit
Due to a comment below I'm adding a trick with invalidating a primary key.

You could also go a way of invalidating the primary key by this trick:

SELECT u.id, 
       u.application_id,
       u.login,
       u.phone,
       #...
FROM users as u
WHERE u.application_id = 1234
ORDER BY u.id+0

user_626692 · Answer

It's hard to be certain, but in very many cases, MySQL is good at selecting the correct index.

It may be necessary to update the statistics for the query analyzer - occasionally the query analyzer doesn't have good information on the data, and this can lead to weird behaviour.

However...your index_users_on_application_id only has application_id, but not ID.

I'm guessing that application_id has a fairly low number of distinct values. ColumnID is unique, and has as many values as there are rows in the table. There is no index that includes both the where clause and the order by clause, so MySQL guesses that the most expensive part will be ordering by ID, rather than filtering by application_id.

So, I think MySQL is doing the right thing here. Behind the scenes, it's returning all the rows ordered by ID, and going through that list and giving you the first 10 with the specified application_ID.

The obvious thing to do is to create an index with application_id and ID, and see if MySQL picks that.

user_5518513 · Answer

When you use ORDER BY column_name MySQL scan all data in column given in ORDER BY clause, here in your case id column will get scan. For this database use Table pointer. In InnoDB, it is the value of the PRIMARY KEY and in MyISAM, it is an offset in the .MYD file.

That's why when you use ORDER BY id your query start using only primary key as index.

To use you created index add id column as first column of index. Then it will use index effectively. So your index unique_index_users_on_application_id_and_login on users table should contains columns as follows in same sequence 1- id & 2- application_id.

For more details about MySQL performance of ORDER BY/ LIMIT Go here

MySQL uses incorrect index. Why?

3 Answers