Explain how a B-tree index works in MySQL and when an index will not be used.

An InnoDB index is a B+ tree: keys are held in sorted order, all values live in the leaf nodes, and the leaves are linked so a range scan can walk sideways without returning to the root. Because it is sorted, the engine can find a value in roughly log(n) page reads instead of scanning every row.

Two kinds matter:

• The clustered index is the primary key, and the full row is stored in its leaves. There is exactly one per table.
• A secondary index stores the indexed columns plus the primary key value, so using one usually costs a second lookup back into the clustered index unless the index covers every column the query needs.

An index will be ignored when:

• You wrap the column in a function — WHERE YEAR(created_at) = 2024 cannot use an index on created_at, but WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' can.
• You skip a leading column of a composite index. An index on (a, b, c) serves WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ? alone.
• The pattern is leading-wildcard, like LIKE '%term'.
• There is an implicit type conversion between the column and the value.
• The optimiser estimates the query will match a large fraction of the table, in which case a sequential scan is genuinely cheaper.