Does index need to cover all columns selected? 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&gt; 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&gt; 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&gt; 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?
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).