MySQL Composite Indexes
This article describes composite indexes in MySQL, that is, indexes built on multiple columns.
In MySQL, a composite index is also called a composite index or a multi-column index, which is an index on multiple columns. MySQL composite indexes allow you to use up to 16 columns.
MySQL Composite Index Syntax
To create a composite index, use the CREATE INDEX statement:
CREATE INDEX index_name
ON table_name(column_1, column_2, column_3);
Here, an index named index_name is created on the column_1, column_2, and column_3 3 columns.
MySQL Composite Index Rules
When defining a multi-column index, the commonly used columns in the WHERE clause placed at the beginning of the columns list, and the uncommonly used columns should be placed in the following. Otherwise, the MySQL optimizer may not use the index.
For example, the following statement defines an index on the a, b and c columns:
CREATE INDEX index_name
ON table_name(a, b, c);
In the above syntax, the MySQL optimizer will consider using indexes in the following cases:
WHERE a = v1 and b = v2 and c = v3;
or
WHERE a = v1 and b = v2;
or
WHERE a = v1;
However, indexes are not considered for use in the following cases:
WHERE c = v3;
or
WHERE b = v2 and c = v3;
That is, if the condition columns do not form the leftmost prefix of the index, the query optimizer cannot use the index to perform lookups.
MySQL multi-column index example
Let ’s demonstrate a MySQL multi-column index in the customer table from the Sakila sample database .
Use the following statement to view the customer table information:
DESC customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| email | varchar(50) | YES | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| create_date | datetime | NO | | NULL | |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+Use the SHOW INDEXES statement to view the indexes in the customer table:
SHOW INDEXES FROM customer;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customer | 0 | PRIMARY | 1 | customer_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_store_id | 1 | store_id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_address_id | 1 | address_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name | 1 | last_name | A | 598 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)Several indexes already exist in the customer table, in order to demonstrate the multi-column index, we delete the idx_last_name index first:
DROP INDEX idx_last_name ON customer;
Create an index named idx_last_name_first_name on last_name and first_name columns using the following statement:
CREATE INDEX idx_last_name_first_name
ON customer (last_name, first_name);
View the information of the customer table:
SHOW INDEXES FROM customer;
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customer | 0 | PRIMARY | 1 | customer_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_store_id | 1 | store_id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_address_id | 1 | address_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name_first_name | 1 | last_name | A | 598 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name_first_name | 2 | first_name | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)We found the customer_last_name_first_name_idx index is defined on last_name and first_name columns .
Use the EXPLAIN statement check whether the following statements use indexes:
EXPLAIN
SELECT * FROM customer
WHERE last_name = 'A'
AND first_name = 'B';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | ref | idx_last_name_first_name | idx_last_name_first_name | 364 | const,const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)Here, when the WHERE condition has last_name and first_name, the MySQL optimizer chooses to use the index.
Use the EXPLAIN statement check whether the following statements use indexes:
EXPLAIN
SELECT * FROM customer
WHERE last_name = 'A';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | ref | idx_last_name_first_name | idx_last_name_first_name | 182 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)Here, when there is only last_name in the WHERE condition, the MySQL optimizer chooses to use the index. This is because last_name the column is the first column in the index.
Use the EXPLAIN statement check whether the following statement use indexes:
EXPLAIN
SELECT * FROM customer
WHERE first_name = 'B';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 599 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Here, when there is only first_name in the WHERE condition, the MySQL optimizer does not chooses to use the index. This is because the first_name column is not the first column of the index.
Conclusion
This article discusses MySQL multicolumn indexes and how the order of multicolumn indexes affects the MySQL optimizer.
When you define a multi-column index, you should always consider the business context to determine which columns are frequently used for lookups, and place those columns at the beginning of the column list when defining the index.