CODEWITHSUNDEEP
mysqlsqlindexing
membersound
membersound

Reputation: 86855

Does mysql need additional indexes for queries on subset of existing index?

If I have an index on 4 column:

CREATE TABLE mytable (
   from varchar(20) NOT NULL,
   to varchar(20) NOT NULL,
   departure date NOT NULL,
   arrival date NOT NULL,
   ....
   KEY mykey(from, to, departure, arrival)
);

When I do a query for equalness on only from and to, would I have to create an additional index KEY fromto (from, to) so that mysql is using an index for the scan?

Or would mysql reuse the existing index, as I'm only querying a subset of it with SELECT * from mytable where from = 'New' and to = 'York'? Or even only a query on ...from = 'New'?

Upvotes: 1

Views: 174

Answers (2)

Ersoy
Ersoy

Reputation: 9604

Mysql documentation says;

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

you may use explain to see whether the index is used or not.

mysql> explain select * from mytable where `from` = 'a' and `to` = 'b';
+----+-------------+---------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | ref  | mykey         | mykey | 164     | const,const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

Upvotes: 2

Gordan Bobić
Gordan Bobić

Reputation: 1888

No, you don't need an additional index. As long as the prefix part of an index can be used, the index can be used.

If you have an index on (from, to, departure, arrival), you can use this index for any query that has an equality match on:

(from)

(from, to)

(from, to, departure)

(from, to, departure, arrival)

in any order.

Upvotes: 1

Related Questions