You found the slow query. You added an index. You ran it again, full of hope.

Still slow.

An index is not a magic speed switch. It's a sorted structure that MySQL can only use when your query is shaped the right way. Shape it wrong and MySQL politely ignores your index and reads the whole table, every time, without telling you. These are the seven mistakes I see most often, in roughly the order they tend to bite.

First, a 30-second mental model

A B-tree index is like the index at the back of a book, sorted by the indexed value. If you know the exact word, or a range of words starting with "con", you can jump straight to the right page. If you're looking for every word that ends in "tion", the sorted order is useless and you're back to reading every page.

Almost every mistake below is a version of that: asking a question the sorted order can't answer.

1. Wrapping the column in a function

This one looks completely innocent:

-- Can't use an index on created_at
SELECT * FROM payments WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

The index stores created_at values, not YEAR(created_at) values. To answer the question MySQL has to compute the function for every row. Rewrite it as a range on the raw column:

SELECT * FROM payments
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Same result, and now it's an index range scan. In Laravel, watch out for whereYear() and whereDate(): they're convenient, but they generate exactly this function-wrapped SQL. whereBetween() or two where() calls keep the index usable. (The LOWER(email) case usually isn't needed at all: MySQL's default collations are already case-insensitive. If you truly need it, MySQL 8.0.13+ supports functional indexes.)

2. Searching with a leading wildcard

WHERE name LIKE 'Moh%' can use an index. WHERE name LIKE '%med' cannot. That's the book-index problem exactly: the values are sorted from the first character, so a pattern that starts with "anything" gives MySQL nowhere to begin.

If you need "contains" search on real text, look at a FULLTEXT index or a proper search engine. If it's a small table, accept the scan and stop worrying.

3. Composite index columns in the wrong order

An index on (tenant_id, status, created_at) is sorted by tenant_id first, then status within each tenant, then date. MySQL can use it from the left: tenant_id, or tenant_id + status, or all three. It cannot jump in at status alone. This is the leftmost prefix rule.

The practical rule of thumb for ordering:

• Columns you filter with equality (=, IN) go first.
• The column you filter by range or sort by goes last. Once MySQL hits a range, the columns after it can't be used for seeking.

// Query: where('tenant_id', $id)->where('status', 'open')->latest()
$table->index(['tenant_id', 'status', 'created_at']);

4. Indexing a column with only a few distinct values

An index on is_active where most rows are active barely helps. If the condition matches a large share of the table, reading the index and then jumping back to the table for each row is slower than a straight scan, and the optimizer knows it. It will often just skip your index.

Low-selectivity columns still earn their place, but as part of a composite index next to something selective, not on their own.

5. Adding an index for every query

The opposite mistake. Every index is a second copy of some data that must be updated on every INSERT, UPDATE and DELETE. It also costs memory in the buffer pool. Ten single-column indexes on one table make writes slower and usually help reads less than two well-designed composite ones.

Look for redundancy: if you have (tenant_id, status), a separate index on tenant_id alone is usually dead weight.

6. Comparing different types

This one is sneaky. A phone column is a VARCHAR, and the query does this:

SELECT * FROM tenants WHERE phone = 97455501234;  -- number, not string

When MySQL compares a string column with a number, it converts the column values to numbers, row by row. The index on phone becomes useless. Quote the value and it's instant again. The same family of problems shows up when joining columns with different character sets or collations, or a signed INT to a BIGINT UNSIGNED foreign key. Keep the types identical on both sides.

7. Guessing instead of running EXPLAIN

The real mistake behind the other six is not checking. EXPLAIN tells you what MySQL actually plans to do:

EXPLAIN SELECT * FROM payments
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC;

The columns worth reading first:

• type: ALL means a full table scan. ref, range and const are what you want.
• key: the index it chose. NULL means none.
• rows: an estimate of how many rows it will examine.
• Extra: Using filesort or Using temporary are hints that your index doesn't cover the sort.

On MySQL 8.0.18 and later, EXPLAIN ANALYZE actually runs the query and shows real timings per step, which is even better.

A quick checklist before you add the next index

1. Is the indexed column used raw, without a function around it?
2. Does any LIKE pattern start with a fixed prefix?
3. Are equality columns first and the range or sort column last?
4. Is the column selective enough to be worth it on its own?
5. Does an existing composite index already cover it?
6. Do the types and collations match exactly?
7. What does EXPLAIN say, before and after?

Which of these has cost you the most time? My vote for the sneakiest is the type mismatch, because the query looks perfect on paper. I'm curious which one got you.