Why Your Database Index Gets Ignored (and How to Design One That Isn't)

TL;DR: An index can exist and still do nothing for your query. A multi-column index only serves queries that use its columns from the left, in the index's order. Fix it by putting the column you filter on first. Go further by putting every column the query needs inside the index (a covering index) so the database never touches the table. But every index taxes every write, so design them, don't collect them.

The setup

Last time I showed what happens with no index: the database reads every row. This is the sneakier version.

You added the index. EXPLAIN still says the table is being scanned. The index isn't broken, and the database isn't being dumb. The index just cannot serve that query.

CREATE INDEX idx_name ON users(last_name, first_name);