This content originally appeared on DEV Community and was authored by Ujjwal Tyagi
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
| Query | Index Used | Time (ms) | Rows Examined | Note |
|---|---|---|---|---|
city = 'Mumbai' AND age = 30 |
city, age | ~10 | ~400 | Perfect match |
age = 30 AND city = 'Mumbai' |
city, age | ~200 | ~14,000 | Wrong order |
city = 'Mumbai' ORDER BY age |
city, age | ~9 | ~450 | Filtering + sorting |
city = 'Mumbai' ORDER BY created_at LIMIT 50 OFFSET 10000 |
city, created_at | ~20 | ~10,050 | Efficient pagination |
💡 Learnings
- Composite indexes must follow the left-to-right rule. You can’t skip columns in the order.
- Use
EXPLAINto 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!
This content originally appeared on DEV Community and was authored by Ujjwal Tyagi
Ujjwal Tyagi | Sciencx (2025-05-08T05:27:15+00:00) Indexing Deep Dive: Composite Indexes & Real Query Experiments. Retrieved from https://www.scien.cx/2025/05/08/indexing-deep-dive-composite-indexes-real-query-experiments/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.