This content originally appeared on DEV Community and was authored by meet
When creating composite indexes for queries in MySQL, the correct order of columns is crucial for performance optimization. We often focus on columns with higher cardinality and place them first in the index, assuming it will boost performance.
Example: Tasks Table
Consider a tasks table that stores details of tasks assigned to users, with a large volume of data:
SELECT * FROM tasks
WHERE user_id = ? AND creation_date BETWEEN ? AND ?;
A typical approach might be to create an index like this:
(creation_date, user_id)
But cardinality is not the only factor to consider when creating composite indexes. A wrongly ordered index can lead to significant performance issues, such as slower queries and even full table scans.
Why Cardinality Isn’t the Only Consideration
Although creation_date might have high cardinality, the query structure (equality vs. range vs. sorting) plays a significant role in determining the optimal index order.
Since user_id is used in an equality condition, it should come first in the index as it is highly selective. The range condition on creation_date (e.g., BETWEEN ? AND ?) should follow because range conditions are less efficient than equality checks.
Correct Index:
(user_id, creation_date)
Learnings:
- Start with equality filters, followed by range filters, and end with columns used for sorting.
- Without sorting inclusion, MySQL may have to perform an extra sort on the result set.
- Always test with the specific queries using production data and EXPLAIN plan to find the optimal index for your workload.
This content originally appeared on DEV Community and was authored by meet
meet | Sciencx (2025-08-26T08:45:48+00:00) Composite Indexes in MySQL. Retrieved from https://www.scien.cx/2025/08/26/composite-indexes-in-mysql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.