Indexes Are Not Magic — They Are Engineering
Most developers know that indexes speed up queries. But far from all understand how exactly they work, when they do not work, and how to create them properly.
How B-Tree Indexes Work
MySQL uses B+Tree indexes by default in InnoDB. For a table with 10 million rows, a B-Tree search requires approximately 23 comparisons, while a full scan needs 10 million.
The Most Common Mistake: Indexing Everything
Each index increases INSERT/UPDATE/DELETE time because MySQL must update all indexes when data changes. The right approach — index only columns regularly used in WHERE, JOIN, ORDER BY, and GROUP BY.
Composite Indexes and Column Order
A composite index on (last_name, first_name, age) works for queries by last_name, by last_name + first_name, and by all three. But it does NOT work for queries by first_name only. This is the leftmost prefix rule.
Covering Indexes
If all columns needed by a query are contained in the index, MySQL does not access the table itself. Covering indexes are especially effective for COUNT queries and aggregations.
Full-Text Search vs LIKE
The LIKE operator does not use indexes — it always results in a full scan. For text search, use FULLTEXT indexes.
EXPLAIN — Your Best Friend
Always check queries through EXPLAIN before considering them optimized.
Conclusion
Indexing is a balance between read and write speed. Analyze with EXPLAIN, understand B+Tree, follow the leftmost prefix rule — and your queries will fly.