Indexing Deep Dive: Composite Indexes & Real Query Experiments

We pushed MySQL indexing further with composite indexes and pagination queries. Here’s what we discovered.
tags: [MySQL, Indexing, Database Performance, Experiments, Composite Index]

🧠 Quick Recap (for New Readers)

In our last blog, we showed how adding a simple index on a single column sped up a basic query on 80,000 rows from 420ms to 8ms.

This time, we’re experimenting with composite indexes and pagination queries to simulate more real-world scenarios.

🧪 Experiment Setup

We kept the same dataset of 80,000 records, but added queries that reflect actual use-cases: filtering by multiple columns, sorting, and paginating.

🔧 Table Structure (Same as Before):

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  age INT,
  city VARCHAR(255),
  created_at DATETIME
);

🧪 Experiments & Results

🧪 Experiment 1: Composite Index on (city, age)

Query:

SELECT * FROM users WHERE city = 'Mumbai' AND age = 30;

Without Index:

• Execution Time: ~430ms
• Rows Examined: ~80,000 (Full Table Scan)

With Index:

CREATE INDEX idx_city_age ON users(city, age);

• Execution Time: ~10ms
• Rows Examined: ~400

✅ Result: Drastic improvement when filtering by both fields in the same order as the index.

🧪 Experiment 2: Reversed Order in Query (Still using same index)

Query:

SELECT * FROM users WHERE age = 30 AND city = 'Mumbai';

• Execution Time: ~200ms
• MySQL did not fully utilize the composite index
• Rows Examined: ~14,000

⚠️ Insight: Composite indexes only work efficiently left to right.

🧪 Experiment 3: Sorting with Index

Query:

SELECT * FROM users WHERE city = 'Mumbai' ORDER BY age;

• Without Index: ~350ms, full table scan then sort
• With Composite Index (city, age): ~9ms

✅ Index helps with both filtering and ordering if the sort column is included in index order.

🧪 Experiment 4: Pagination Query

Query:

SELECT * FROM users WHERE city = 'Mumbai' ORDER BY created_at LIMIT 50 OFFSET 10000;

• Without Index: ~500ms
• With Index on (city, created_at): ~20ms

✅ Pagination becomes significantly faster when an index is used for both filter and sort.

📊 Summary Table

💡 Learnings

• Composite indexes must follow the left-to-right rule. You can’t skip columns in the order.
• Use EXPLAIN to check which indexes are used.
• Composite indexes work beautifully when your query filters and sorts in the indexed order.
• For large OFFSETs, indexes reduce the load drastically.

🔮 Coming Up Next

In the next post, we’ll explore:

• Covering indexes
• Multi-index selection
• Index merge strategies

If you’ve ever wondered how multiple indexes interact or why MySQL sometimes ignores your index, don’t miss it.

💬 Got any indexing questions or edge cases you want tested? Drop a comment!