MySQL Indexes: Boosting Query Performance with Column Order

In MySQL, the order of columns in a composite index (an index that spans multiple columns) matters. The index is optimized based on the order of the columns, and this affects how MySQL can efficiently use the index for different queries. Let’s break down the two scenarios and how they perform differently based on the query.

Index 1: CREATE INDEX index_name ON table_name (column1, column2);

• This creates an index on column1 as the leading (first) column and column2 as the second column.
• This index will optimize queries that:
• Filter by column1.
• Filter by both column1 and column2.
• Use a range condition on column1, such as BETWEEN or >.

Query: SELECT * FROM table_name WHERE column1 = 'xx';

• This query can fully utilize the index, as it starts with column1, which matches the leading column in the index.
• MySQL will use this index to quickly find rows where column1 = 'xx' without having to scan the entire table.

Query: SELECT * FROM table_name WHERE column2 = 'xx';

• In this case, MySQL cannot use the index efficiently. Since the index starts with column1, MySQL cannot skip to column2 without first filtering by column1.
• As a result, MySQL would perform a full table scan or possibly use another index (if available) to filter column2 = 'xx'.

Difference in Index Usage

• Leading Column Matters: MySQL can only use the index if the query filter matches the leading column of the composite index. If the filter does not involve the leading column, MySQL will not use the index.
• Index Efficiency:
• Index (column1, column2) is optimized for queries filtering by column1 or both column1 and column2.
• Index (column2, column1) is optimized for queries filtering by column2 or both column2 and column1.

Summary of Query Performance

Conclusion

It means, in our system, let’s check the index first, see its order, and place the arguments in the correct place, it will typically increase the query performance without spending more effort.

• If you query column1 frequently, the index on (column1, column2) will work well.
• If you query column2 more often, then the index on (column2, column1) will be more efficient.
• Composite indexes are highly order-dependent, and you should choose the column order based on which columns are more frequently queried or filtered.

Read more: https://dev.mysql.com/doc/refman/8.4/en/mysql-indexes.html