Why Composite indexes in MySQL? - Purpose & Use Cases

The Big Idea

What if your database could find combined info instantly, like flipping to the exact page in a book?

The Scenario

Imagine you have a huge phone book and you want to find people by both their last name and city. Without any special help, you have to flip through every page, checking each name and city one by one.

The Problem

Doing this by hand is slow and tiring. You might miss some entries or take a long time to find what you want. In databases, searching without the right tools means the computer works harder and slower, especially when many people ask at once.

The Solution

Composite indexes act like a smart guide that sorts the phone book first by last name, then by city. This way, the database quickly jumps to the right section, making searches much faster and more accurate.

Before vs After

✗ Before

SELECT * FROM contacts WHERE last_name = 'Smith' AND city = 'Boston'; -- no index, full scan

✓ After

CREATE INDEX idx_name_city ON contacts(last_name, city);
SELECT * FROM contacts WHERE last_name = 'Smith' AND city = 'Boston';

What It Enables

Composite indexes let databases find combined information quickly, making apps feel faster and more responsive.

Real Life Example

Online stores use composite indexes to quickly find products by category and price range, so you see what you want without waiting.

Key Takeaways

Searching multiple columns without indexes is slow and inefficient.

Composite indexes speed up queries that filter by several columns together.

They help databases handle complex searches smoothly and quickly.