MySQL Indexing Masterclass: B-Tree Mechanics, Composite Indexes & Tuning
Accelerate slow database queries in MySQL: B-Tree structure, Clustered vs Secondary indexes, Leftmost prefix rule, and EXPLAIN query plan analysis.
Database
-- Creating an optimal composite index for student search queries
CREATE INDEX idx_student_status_city
ON students (status, city, enrollment_year);
-- Analyzing query execution plan
EXPLAIN ANALYZE
SELECT full_name, email
FROM students
WHERE status = 'ACTIVE' AND city = 'Ghaziabad'
ORDER BY enrollment_year DESC;1. How B-Tree Indexes Work
In MySQL InnoDB, data is stored in 16KB pages structured as a balanced B+ Tree. An index allows the storage engine to perform logarithmic binary traversals to find rows without scanning the entire table sequentially.
2. The Leftmost Prefix Rule
For composite indexes across multiple columns (A, B, C), the index can accelerate queries filtering by (A), (A, B), or (A, B, C), but cannot be used if the query only filters by (B) or (C).
Key Engineering Takeaways
- InnoDB primary keys form the Clustered Index; rows are physically ordered by the primary key.
- Secondary indexes store the primary key value as their pointer to the row.
- Do not over-index; each index adds write overhead to INSERT, UPDATE, and DELETE queries.
Frequently Asked Questions
What does "Using filesort" mean in an EXPLAIN plan?
It indicates MySQL cannot use an index to satisfy an ORDER BY clause and must sort rows in an extra memory buffer.
Master This in Our Ghaziabad Coding Labs
Gain deep mastery over these concepts with direct instructor guidance, code reviews, and mock interviews at C-60 R.K Tower, 3rd Floor, RDC, Ghaziabad.
Explore Related Course Call 7503962162