Add a Composite Index for Multi-Column Queries
In this step, you will learn how to create a composite index in MySQL. A composite index is an index on two or more columns in a table. It can significantly improve the performance of queries that filter data based on multiple columns.
What is a Composite Index?
A composite index is an index that covers multiple columns. It is useful when queries frequently use multiple columns in the WHERE clause. The order of columns in the composite index is important. The index is most effective when the columns are specified in the same order in the query's WHERE clause.
Let's continue using the users table we created in the previous steps. Open a terminal in the LabEx VM (using the Xfce Terminal shortcut on the desktop) and connect to the MySQL server as the root user:
mysql -u root -proot
Let's add some more data to the users table, including different cities:
INSERT INTO users (username, email, city) VALUES
('alice_brown', '[email protected]', 'Los Angeles'),
('bob_davis', '[email protected]', 'Chicago'),
('charlie_wilson', '[email protected]', 'New York'),
('david_garcia', '[email protected]', 'Los Angeles');
Creating a Composite Index
Suppose you often run queries that filter users by both city and username. In this case, you can create a composite index on the city and username columns.
CREATE INDEX idx_city_username ON users (city, username);
This statement creates an index named idx_city_username on the city and username columns of the users table.
Verifying the Index
You can verify that the index has been created by using the SHOW INDEXES command:
SHOW INDEXES FROM users;
The output will show the details of the indexes on the users table, including the idx_city_username index you just created. You should see two rows for idx_city_username, one for the city column and one for the username column.
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_username | 1 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 1 | city | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 2 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Using the Composite Index
To see the benefit of the composite index, you can use the EXPLAIN command to analyze a query that uses both the city and username columns in the WHERE clause.
EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';
The EXPLAIN output will show that the query is using the idx_city_username index, which means the database can quickly find the matching row without scanning the entire table. Look for possible_keys and key columns in the output. If the index is being used, you will see idx_city_username in these columns.
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_city_username | idx_city_username | 770 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
Order of Columns in the Index
The order of columns in the composite index matters. If you create an index on (username, city) instead of (city, username), the index will be less effective for queries that filter by city and then username.
For example, if we had an index on (username, city) and ran the following query:
EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';
MySQL might not use the index, or it might only use it partially, because the city column is not the leading column in the index.
exit;
