1

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?

  • 2
    You seem to be using both a phones_tables (ending with s) and a phones_table (without the s). Do you really have the two? Do both have the same indices? – joanolo Jan 18 '17 at 1:03
  • I changed the table names to obscure what's really being used. So that's actually a typo – n00b Jan 18 '17 at 2:00
6

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).

  • Thanks Rick. You're right it was a VARCHAR column and I was querying it with an integer. I would've still thought that the index would be used but changing the query to a string fixes my issue – n00b Jan 18 '17 at 2:22
  • 1
    It only works the other way (string constant automatically converted to integer) – Rick James Jan 18 '17 at 4:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.