15. Index Tuning Secrets: Leftmost Prefix Rule, Covering Indexes (Using Index) & Avoiding Index Degradation Traps • MySQL Mastery: Basic to Advanced & Enterprise Scaling • Rishabh Ki Kaksha

MySQL Mastery: Basic to Advanced & Enterprise Scaling › 15. Index Tuning Secrets: Leftmost Prefix Rule, Covering Indexes (Using Index) & Avoiding Index Degradation Traps

45 mins

← Prev Next →

Course Syllabus

Module 1: Relational Algebra, SQL Fundamentals & Schema Design

01. Relational Database Foundations: SQL vs NoSQL, Relational Algebra & Database Normalization (1NF to BCNF) 02. MySQL Data Types Deep Dive: VARCHAR vs TEXT, DECIMAL for Financial Precision & JSON Column Performance 03. Primary Keys, Foreign Keys, Unique Constraints & Referential Integrity Rules (CASCADE / RESTRICT)

Module 2: Advanced SQL: Joins, Window Functions & CTEs

04. SQL Joins Masterclass: INNER, LEFT, RIGHT, FULL OUTER & Cross Joins Execution Strategies 05. Subqueries vs Common Table Expressions (WITH CTE) & Recursive CTEs for Hierarchical Tree Queries 06. SQL Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD() & LAG() for Analytics

Module 3: InnoDB Storage Engine Internals, Buffer Pool & B+ Trees

07. InnoDB Architecture: The Buffer Pool, Change Buffer, Redo Log (WAL), Undo Log & Doublewrite Buffer 08. Clustered Indexes vs Secondary Indexes: How B+ Tree Data Pages are Organized on Disk 09. The B+ Tree Node Structure: Fanout, Height Calculations & Why B+ Trees Outperform Binary Trees for Disk I/O

Module 4: ACID Transactions, Lock Architecture & Concurrency Control

10. ACID Properties Deep Dive: Atomicity (Undo Log), Consistency, Isolation & Durability (Redo Log) 11. Transaction Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read (Default) & Serializable 12. Multi-Version Concurrency Control (MVCC): Read Views, Row Versioning & Non-Locking Consistent Reads 13. InnoDB Locking Engine: Record Locks, Gap Locks, Next-Key Locks & Resolving Deadlocks in High Concurrency

Module 5: Query Optimization, Index Tuning, Partitioning & High Availability

14. Query Optimization with EXPLAIN & EXPLAIN ANALYZE: Type column (ALL, index, range, ref, eq_ref, const) 15. Index Tuning Secrets: Leftmost Prefix Rule, Covering Indexes (Using Index) & Avoiding Index Degradation Traps 16. Table Partitioning: Range, List, Hash & Key Partitioning for Multi-Million Row Tables 17. MySQL Replication: Binary Log (binlog), Master-Slave Async vs Semi-Sync Replication & GTID Architecture 18. Production Enterprise Backup Strategies: Physical (Percona XtraBackup) vs Logical (mysqldump / mydumper)

Interactive Lesson Studio

# 15. Index Tuning Secrets: Leftmost Prefix Rule, Covering Indexes (Using Index) & Avoiding Index Degradation Traps

> MySQL Mastery Notes by Rishabh - Topic 15

---

### 1. The Leftmost Prefix Rule 🧭

Given composite index `INDEX(a, b, c)`:
*   `WHERE a = 1` $\implies$ ✅ Uses index ($a$).
*   `WHERE a = 1 AND b = 2` $\implies$ ✅ Uses index ($a, b$).
*   `WHERE a = 1 AND b = 2 AND c = 3` $\implies$ ✅ Uses index ($a, b, c$).
*   `WHERE b = 2` $\implies$ ❌ **Cannot use index!** (Leftmost prefix $a$ is missing).
*   `WHERE a = 1 AND c = 3` $\implies$ ⚠️ Uses index only for $a$; $c$ is filtered via index condition pushdown.

---

### 2. Covering Indexes (`Using index` - Zero Table I/O ⚡)

Jab query ke saare requested columns (`SELECT id, email FROM users WHERE email = 'test@example.com'`) index structure me hi maujood hon $\rightarrow$ **MySQL actual disk table page ko touch bhi nahi karta! (100% Index Only Scan!)**

---

### 3. 5 Classic Index Invalidation Traps ⚠️

1. **Function on column:** `WHERE YEAR(created_at) = 2026` ❌ $\rightarrow$ Use `WHERE created_at >= '2026-01-01'` ✅.
2. **Implicit Type Conversion:** `WHERE phone_varchar = 9876543210` ❌ $\rightarrow$ Use quotes `'9876543210'` ✅.
3. **Leading Wildcard LIKE:** `WHERE name LIKE '%john'` ❌ (Breaks B+ Tree) $\rightarrow$ `WHERE name LIKE 'john%'` ✅.
4. **`OR` conditions with unindexed columns:** `WHERE indexed_col = 1 OR unindexed_col = 2` ❌.
5. **Inequality breaks subsequent index parts:** `WHERE a = 1 AND b > 10 AND c = 5` (Index used only for $a, b$; column $c$ cannot use index).

---
**Next Topic:** 16. Table Partitioning: Range, List, Hash & Key Partitioning for Multi-Million Row Tables

Loading lesson content...

MySQL

Full IDE

Live Execution Terminal

Click "Execute Live" to run code.
                

Lesson Mastery AI Quiz

AI Doubt Solver

CERTIFICATE OF COMPLETION

Rishabh Gehlot

PHP 8+ & Laravel 11 Master Track