In this lesson, you will learn how to leverage the "left-most prefix rule" to structure composite indexes for maximum query performance.
Understanding Composite Indexes
A composite index (or multi-column index) is an index created on two or more columns of a table. While a single-column index helps you find data based on one attribute, a composite index is a powerful tool for queries that filter or sort by multiple criteria simultaneously.
The Left-Most Prefix Rule
The most critical rule when using composite indexes in MySQL and MariaDB is the left-most prefix rule. When you define an index on columns (A, B, C), the database engine can use this index for lookups that follow this specific order:
(A)(A, B)(A, B, C)
The database cannot effectively use this index if your query only filters by (B) or (C), or even (B, C). Think of it like a telephone book: it is sorted by Last Name, then First Name. You can easily find someone if you know the Last Name, but if you only have the First Name, the index (the book’s ordering) provides no shortcut.
Practical Illustration
Imagine a users table with a composite index on (last_name, first_name, birth_date).
The following query works efficiently because it matches the left-most prefix:
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
However, the following query cannot use the index for filtering:
SELECT * FROM users WHERE first_name = 'John';
Even though first_name is in the index, it is not the "left-most" column. The engine would be forced to perform a full table scan rather than an index seek, significantly increasing execution time.
Design Strategy
When designing your indexes, place the columns you filter by most frequently (especially those using equality operators like =) at the far left of the composite index. If you have columns with high cardinality—meaning they contain many unique values—they should generally appear earlier in the index to help the database narrow down the result set as quickly as possible.
Key Takeaway: The database can only use a composite index if your query utilizes the columns starting from the left; always order your composite index columns to match your most common query filter patterns.