Multiple-Column Indexes on MySQL
MySQL can use multiple-column indexes for queries that use all the columns, the first two columns, the first three columns and so on.
MySQL :: MySQL 5.7 Reference Manual :: 9.3.5 Multiple-Column Indexes
The following index is equivalent to
INDEX(columnA, columnB, columnC)
the following inedex
INDEX(columnA, columnB, columnC) INDEX(columnA, columnB) INDEX(columnA)
See the rows of the following execution plans:
mysql> CREATE TABLE test ( -> id INT NOT NULL, -> last_name CHAR(30) NOT NULL, -> first_name CHAR(30) NOT NULL, -> PRIMARY KEY (id), -> INDEX name (last_name,first_name) -> ); Query OK, 0 rows affected (0.04 sec) mysql> INSERT test (id, last_name, first_name) VALUES (1, 'last', 'first'), (2, 'last2','first2'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test where last_name = 'last' and first_name = 'first'; +----+-----------+------------+ | id | last_name | first_name | +----+-----------+------------+ | 1 | last | first | +----+-----------+------------+ 1 row in set (0.00 sec) mysql> explain select * from test where last_name = 'last' and first_name = 'first'; +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 180 | const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test where last_name = 'last' ; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | ref | name | name | 90 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test where first_name = 'first'; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | index | NULL | name | 180 | NULL | 2 | 50.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>