When Composite Indexes Fail in MySQL: Conditions and Examples

MySQL composite indexes (also called multi‑column indexes) are built on two or more columns and are used from left to right; a query can only use the leftmost contiguous columns of the index. For example, an index on (a,b,c) can be used for a, a,b, or a,b,c, but not for b,c alone.

When the leftmost column is a constant, the index is highly effective. Using additional columns narrows the search range, but a composite index behaves differently from two separate single‑column indexes.

Consider the following table definition:

create table myTest(
    a int,
    b int,
    c int,
    KEY a(a,b,c)
);

Various SELECT statements illustrate when the index is applied:

select * from myTest where a=3 and b=5 and c=4;  -- uses a,b,c (order matches index)

MySQL automatically reorders WHERE conditions, so the same result is obtained with:

select * from myTest where c=4 and b=6 and a=3;

If a range condition appears on a column before the last indexed column, the index is stopped (a "break point"). Example:

select * from myTest where a=3 and b>7 and c=3;  -- a uses index, b is a range, c cannot use index

When the leftmost column is omitted, the composite index cannot be used:

select * from myTest where b=3 and c=4;  -- neither b nor c can use the index because a is missing

Ordering by a column that is part of the index can also benefit sorting, but ordering by a later column after a break point forces a filesort:

select * from myTest where a=3 order by b;  -- a uses index, b can be sorted using the index
select * from myTest where a=3 order by c;  -- index cannot sort by c due to break point

Common reasons for index loss include:

Applying functions, calculations, or type conversions on indexed columns.

The storage engine cannot use the right‑hand column of a range condition.

Not using covering indexes (selecting columns not in the index).

Using inequality operators (!= or <>), which prevent index usage.

Using IS NULL / IS NOT NULL on indexed columns.

LIKE patterns that start with a wildcard (e.g., '%abc%').

To keep indexes effective, choose the most selective columns first in a composite index, include as many WHERE columns as possible, prefer covering indexes, and avoid operations that cause implicit type conversion or break the left‑most prefix rule.