MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
In production databases, queries rarely filter on a single column. Real-world applications filter by status and date, or user ID and order state. Creating multiple single-column indexes is inefficient because MySQL typically uses only one index per table scan.
A Composite Index (multi-column index) indexes multiple columns in a single B+Tree. However, to leverage it effectively, you must understand the Leftmost Prefix Rule.
In this 3-column composite index:
status.status, rows are sorted by order_date.status and order_date, rows are sorted by customer_id.A composite index can only be utilized by the query optimizer if the query filters include the leftmost column of the index definition, in unbroken sequence:
| Query WHERE Clause | Can Use idx_status_date_cust? | Reason |
|---|---|---|
WHERE status = 'PAID' | YES | Matches leftmost prefix (status) |
WHERE status = 'PAID' AND order_date = '2026-08-01' | YES | Matches 2 leftmost columns in sequence |
WHERE status = 'PAID' AND customer_id = 42 | PARTIAL | Uses index for status only; filters customer_id in memory |
WHERE order_date = '2026-08-01' | NO! (Full Scan) | Missing leftmost column (status)! |
WHERE customer_id = 42 | NO! (Full Scan) | Missing leftmost column (status)! |
(LastName, FirstName). You can easily look up everyone named "Smith", or "Smith, John". But you cannot use the book to find everyone whose first name is "John" without scanning every page!When designing composite indexes, the order of columns inside the index definition is paramount:
=) before columns filtered by ranges (>, <, BETWEEN).If you had indexed (status, order_date, customer_id), the range filter on order_date would prevent the index from narrowing down customer_id!
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
| Previous Lesson | Next Lesson |
|---|---|
| Clustered vs Secondary Indexes in MySQL InnoDB | Covering Indexes & Index Condition Pushdown (ICP) |
Test your understanding of this lesson with 5 questions. Each question has one correct answer.