Mysql – Does index need to cover all columns selected

indexMySQL

I'm using an AWS Aurora database (MySQL engine) with a pretty basic query:

SELECT * FROM phones_table where phone_number = 98371381;

There's an index on that table on the phone_number column:

mysql> show index from vaya_call_data_record;
+--------------+------------+--------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| phones_table |          0 | PRIMARY                        |            1 | id             | A         |     4415670 |     NULL | NULL   |      | BTREE      |         |               |
| phones_table |          1 | idx_phones_table_phone_number  |            1 | phone_number   | A         |      183986 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

When I run an explain plan the query doesn't use that index. 

mysql> explain SELECT * FROM phones_table where phone_number = 98371381;
+----+-------------+-----------------------+------+----------------+------+---------+------+---------+-------------+
| id | select_type | table                 | type | possible_keys  | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------------------+------+----------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | phones_table          | ALL  |  NULL          | NULL | NULL    | NULL | 4415670 | Using where |
+----+-------------+-----------------------+------+----------------+------+---------+------+---------+-------------+

If I change the query to only use the phone_number column then the index is used. 

mysql> explain SELECT phone_number FROM phones_table where phone_number = 98371381;
+----+-------------+-----------------------+------+----------------+-------------------------------+---------+------+---------+-------------+
| id | select_type | table                 | type | possible_keys  | key                           | key_len | ref  | rows    | Extra       |
+----+-------------+-----------------------+------+----------------+-------------------------------+---------+------+---------+-------------+
|  1 | SIMPLE      | phones_table          | ALL  |  NULL          | idx_phones_table_phone_number | NULL    | NULL | 4415670 | Using where |
+----+-------------+-----------------------+------+----------------+-------------------------------+---------+------+---------+-------------+

This seems to indicate that I would need all the columns selected in my index, which was not my understanding of how indexes work. Is this a MySQL or even an Aurora specific behavior?

Best Answer

Short answer: No.

Long answer:

  • A "covering" index is usually better than a non-covering one.

  • A covering index for the query you mention would contain all the columns in the table. This is usually a 'bad' idea if there are more than, say, 5 column.

But... I suspect that is not the real problem, nor the real question.

What is the DATATYPE of phonenumber? I suspect it is VARCHAR(...), correct? Yet you are asking for a numeric comparison by saying where phone_number = 98371381.

Add quotes (and make sure it is indexed): where phone_number = "98371381". Based on the table name, I suspect that the that is the PRIMARY KEY? If so, simply PRIMARY KEY(phone_number) is better than any "covering" index.

Always provide SHOW CREATE TABLE when asking performance questions. Else, we have to guess at too many things (datatype, PK, number of columns, engine, etc).

Related Question