Why does MySQL ignore the index even on force for this order by?

Question

I run an EXPLAIN:

mysql> explain select last_name from employees order by last_name;
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 10031 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
1 row in set (0.00 sec)

The indexes in my table:

mysql> show index from employees;  
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |  
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| employees |          0 | PRIMARY       |            1 | subsidiary_id | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |  
| employees |          0 | PRIMARY       |            2 | employee_id   | A         |       10031 |     NULL | NULL   |      | BTREE      |         |               |  
| employees |          1 | idx_last_name |            1 | last_name     | A         |       10031 |      700 | NULL   |      | BTREE      |         |               |  
| employees |          1 | date_of_birth |            1 | date_of_birth | A         |       10031 |     NULL | NULL   | YES  | BTREE      |         |               |  
| employees |          1 | date_of_birth |            2 | subsidiary_id | A         |       10031 |     NULL | NULL   |      | BTREE      |         |               |  
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
5 rows in set (0.02 sec)

There is an index on last_name but the optimizer does not use it.
So I do:

mysql> explain select last_name from employees force index(idx_last_name) order by last_name;  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra          |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 10031 | Using filesort |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
1 row in set (0.00 sec)

But still the index is not used! What am I doing wrong here?
Does it have to do with the fact that the index is NON_UNIQUE? BTW the last_name is VARCHAR(1000)

Update requested by @RolandoMySQLDBA

mysql> SELECT COUNT(DISTINCT last_name) DistinctCount FROM employees;  
+---------------+  
| DistinctCount |  
+---------------+  
|         10000 |  
+---------------+  
1 row in set (0.05 sec)  


mysql> SELECT COUNT(1) FROM (SELECT COUNT(1) Count500,last_name FROM employees GROUP BY last_name HAVING COUNT(1) > 500) A;  
+----------+  
| COUNT(1) |  
+----------+  
|        0 |  
+----------+  
1 row in set (0.15 sec)

score 20 · Answer 1 · edited Jun 15 '20 at 09:05

Actually, the problem here is that this looks like a prefix index. I don't see the table definition in the question, but sub_part = 700? You haven't indexed the whole column, so the index can't be used for sorting and is not useful as a covering index, either. It could only be used to find the rows that "might" match a WHERE and the server layer (above the storage engine) would have to further filter the rows matched. Do you really need 1000 characters for a last name?

update to illustrate: I have a table test table with a litle over 500 rows in it, each with the domain name of a web site in a column domain_name VARCHAR(254) NOT NULL and no indexes.

mysql> alter table keydemo add key(domain_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

With the full column indexed, the query uses the index:

mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | keydemo | index | NULL          | domain_name | 764     | NULL |  541 | Using index |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.01 sec)

So, now, I'll drop that index, and just index the first 200 characters of domain_name.

mysql> alter table keydemo drop key domain_name;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table keydemo add key(domain_name(200));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | keydemo | ALL  | NULL          | NULL | NULL    | NULL |  541 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
mysql>

Voila.

Note also, that the index, at 200 characters, is longer than the longest value in the column...

mysql> select max(length(domain_name)) from keydemo;
+--------------------------+
| max(length(domain_name)) |
+--------------------------+
|                       43 |
+--------------------------+
1 row in set (0.04 sec)

...but that doesn't make any difference. An index declared with a prefix length can only be used for lookups, not for sorting, and not as a covering index, since it doesn't contain the full column value, by definition.

Also, the above queries were run on an InnoDB table, but running them on a MyISAM table yields virtually identical results. The only difference in this case is that the InnoDB count for rows is slightly off (541) while MyISAM shows the exact number of rows (563) which is normal behavior since the two storage engines handle index dives very differently.

I would still assert that the last_name column is likely larger than needed, but it still is possible to index the entire column, if you are using InnoDB and running MySQL 5.5 or 5.6:

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

^{— http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html}

score 9 · Accepted Answer · edited Apr 13 '17 at 12:43

PROBLEM #1

Look at the query

select last_name from employees order by last_name;

I don't see a meaningful WHERE clause, and neither does the MySQL Query Optimizer. There is no incentive to use an index.

PROBLEM #2

Look at the query

select last_name from employees force index(idx_last_name) order by last_name;

You gave it an index, but the Query Opitmizer took over. I have seen this behavior before (How do I force a JOIN to use a specific index in MySQL?)

Why should this happen?

Without a WHERE clause, Query Optimizer says the following to itself:

This is an InnoDB Table
It's an indexed column
The index has the row_id of the gen_clust_index (a.k.a. Clustered Index)
Why should I look at the index when
- there is no WHERE clause?
- I would always have to bounce back to the table?
Since all rows in an InnoDB table reside in the same 16K blocks as the gen_clust_index, I'll do a full table scan instead.

The Query Optimizer chose the path of least resistance.

You are going to be in for a little shock, but here it goes: Did you know that the Query Optimizer will handle MyISAM quite differently?

You are probably saying HUH ???? HOW ????

MyISAM stores the data in a .MYD file and all indexes in the .MYI file.

The same query will produce a different EXPLAIN plan because the index lives in a different file from the data. Why ? Here is why:

The data needed (last_name column) is already ordered in the .MYI
In the worst case, you will have a full index scan
You will only access the column last_name from the index
You do not need to sift through unwanted
You will not trigger temp file creation for sorting

How can be so sure of this? I have tested this working theory on how using a different storage will generate a different EXPLAIN plan (sometimes a better one): Must an index cover all selected columns for it to be used for ORDER BY?

Raymond Nijland · Answer 3 · 2013-09-12T22:32:27.390

I made a answer about because a comment will not support formating and RolandoMySQL DBA talked about gen_clust_index and innodb. And this is very important on a innodb based table. This goes further than normal DBA knowledge because you need to be able to analyse C code..

You should ALWAYS ALWAYS make a PRIMARY KEY or an UNIQUE KEY if you are using Innodb. If you don't innodb will use it's own generated ROW_ID which could do you more harm than good.

I will try explaining it easy because the proof is based on C code.

/**********************************************************************//**
Returns a new row id.
@return the new id */
UNIV_INLINE
row_id_t
dict_sys_get_new_row_id(void)
/*=========================*/
{
    row_id_t    id;

    mutex_enter(&(dict_sys->mutex));

    id = dict_sys->row_id;

    if (0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN)) {
          dict_hdr_flush_row_id();
    }

    dict_sys->row_id++;
    mutex_exit(&(dict_sys->mutex));
    return(id);
}

First problem

mutex_enter(&(dict_sys->mutex));

This line makes sure only one thread can access dict_sys->mutex at the same time. What if already the value was mutexed... yes a thread has to wait so you get something like a nice random feature like thread locking or if you have more tables without your own PRIMARY KEY or UNIQUE KEY then you would have an nice feature with innodb 'table locking ' isn't this not the reason why MyISAM was replaced by InnoDB because off the nice feature thats called record/row based locking..

Second problem

(0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN))

modulo (%) calculations are slow not good if you are batch inserting because it needs to be recalculated every time..., and because DICT_HDR_ROW_ID_WRITE_MARGIN (value 256) is a power of two this could be made much faster..

(0 == (id & (DICT_HDR_ROW_ID_WRITE_MARGIN - 1)))

Side note if the C compiler was configured to optimize and it is an good optimizer, the C optimizer will fix the "heavy" code to the lighter version

motto of the story always create your own PRIMARY KEY or make sure you have a UNIQUE index when you create a table from the start

Why does MySQL ignore the index even on force for this order by?

3 Answers3

PROBLEM #1

PROBLEM #2

Linked