Skip to content

Composite index leftmost prefix principle

The Composite index leftmost prefix principle is a fundamental rule in database indexing (specifically for B+ Tree indexes) that dictates how a multi-column (composite) index is traversed and utilized for query optimization.^[600-developer__mysql.md]

Core Mechanism

The principle states that for a composite index, queries must start with the leftmost column (the first column defined in the index) to effectively traverse the B+ Tree structure^[600-developer__mysql.md]. The database engine matches the query criteria against the index definition from left to right.^[600-developer__mysql.md]

If a query condition specifies the first column, the index can be used to locate the starting point of the data. If the query specifies both the first and second columns, the index can further refine the search. However, if the query skips the first column and searches only by the second column, the index cannot be traversed from the root, rendering it ineffective for that lookup.^[600-developer__mysql.md]

Application in Queries

This principle applies to operations involving the WHERE clause as well as ORDER BY clauses^[600-developer__mysql.md]. To satisfy the leftmost prefix requirement, the column types involved in the query must match the index definition^[600-developer__mysql.md]. For example, if the composite index is defined on columns (A, B), a query filtering by A alone or by A and B can use the index, but filtering only by B cannot^[600-developer__mysql.md].

Additionally, performing operations on indexed columns within the WHERE clause (such as WHERE a + 1 = 5) will cause the index to fail, as the column value is no longer directly compared to the index key^[600-developer__mysql.md].

Sources

^[600-developer__mysql.md]