What is a composite index, and how does column order affect its usage?

For an index on (A, B, C):

MySQL CAN use: A — (A, B) — (A, B, C)

MySQL CANNOT use: B alone, C alone, or (B, C) without A

MySQL can partially use the index if the query includes the leftmost parts.

Composite index: CREATE INDEX idx_user_name_age ON users(last_name, first_name, age);

MySQL can use the index for:

✔ WHERE last_name = 'Doe'

✔ WHERE last_name = 'Doe' AND first_name = 'John'

✔ WHERE last_name = 'Doe' AND first_name = 'John' AND age = 30

MySQL CANNOT use it for:

✘ WHERE first_name = 'John'

✘ WHERE age = 30

✘ WHERE first_name='John' AND age=30 (no leftmost column)