This is for application developers using MySQL to build real world apps. Not DBAs, just folks who want their queries to run fast without digging through dense documentation.
abc% ✅, %abc 🚫
Indexes work for column comparisons using =, >, >=, <, <=, or BETWEEN. They also work with LIKE, but only if the pattern doesn’t start with a wildcard.LIKE 'abc%' is fine, but LIKE '%abc' is not.
AND clauses need to match the index prefix
If your query doesn’t use the leading columns of a composite index, MySQL won’t use that index. The order of your WHERE relative to the index matters. The index should be matched from the start meaning you have to hit the beginning of the index chain for it to be useful. Otherwise, MySQL skips it.
CREATE INDEX idx_name_email
ON users(first_name, last_name, email);
SELECT *
FROM users
WHERE first_name = 'John'
AND last_name = 'Doe'; -- ✅ Uses the index
SELECT *
FROM users
WHERE last_name = 'Doe'
AND email = 'john@example.com'; -- ❌ Does NOT use the index
OR breaks index usage
That is unless all the OR conditions are using the same index. Sometimes MySQL will use an index merge if all conditions have their own indexes. This retrieves the rows using those separate indexes and merges their results into one. This is less effective than using a single scan, and highly dependent on how selective the conditions are or if a full table scan is more efficient.
SELECT * FROM users
WHERE (first_name = 'John' OR first_name = 'Jane'); -- ✅ Uses index
SELECT * FROM users
WHERE (first_name = 'John' OR email = 'john@example.com'); -- 🚫 Cannot use a single index efficiently
Index exists? Doesn’t mean MySQL will use it
Sometimes MySQL doesn't use an index at all even if one is available. This can happen if your index is not selective enough (lots of seeks), meaning scanning the entire table might yield faster results.
Indexes are great, but they can bite you if you're not careful. Follow these rules to get the most out of your indexes.
Top comments (0)