DEV Community

Vishad Patel
Vishad Patel

Posted on

Unlock Faster Queries: A Guide to Composite Indexes in MySQL & PostgreSQL

#sql #database #postgres #mysql

🚨 Problem:

When your SQL queries involve filtering and sorting using multiple columns, relying on individual (single-column) indexes often causes the database to ignore those indexes — or worse, perform a full table scan. This results in slower performance, especially on large datasets.

Let’s say you’re running this query:

SELECT * FROM users 
WHERE country = 'US' AND age > 30 
ORDER BY age;
Enter fullscreen mode Exit fullscreen mode

If you only have individual indexes on country and age, the query planner may not efficiently combine them—leading to high disk I/O and CPU usage as the engine scans more rows than necessary.

✅ Solution: Use a Composite Index That Mirrors the Query Pattern

To optimize this type of query, create a composite (multi-column) index that aligns exactly with the filtering (WHERE) and sorting (ORDER BY) clauses:

CREATE INDEX idx_users_country_age 
ON users(country, age);
Enter fullscreen mode Exit fullscreen mode

This index tells the database engine to store records sorted by country and age, which allows it to:

  • Filter by country
  • Use the index to quickly find rows where age > 30
  • Return results already sorted by age, eliminating the need for an additional sort step

⚙️ Technical Explanation (For Advanced Users):

In most relational databases like PostgreSQL, MySQL, or Oracle, composite indexes work left-to-right, meaning the index is only used efficiently when queries filter on the first column (and optionally the subsequent columns).

  • WHERE country = 'US' AND age > 30 uses the index fully
  • WHERE age > 30 alone won’t use this index efficiently, because age is the second column

Additionally, the optimizer can use the index scan + index-only scan combination if all requested columns are part of the index — further improving performance by avoiding table lookups.

📈 Real-Life Use Case:

Imagine a SaaS admin dashboard where product managers often filter users based on region (country) and age group to target demographics or generate usage reports.

Without composite indexes:

  1. Loading such filtered lists becomes sluggish
  2. Backend API response time increases
  3. Pagination and sorting add extra load

With the proper composite index:

  1. Filtering and sorting happen within the index tree
  2. Query latency drops significantly
  3. APIs remain responsive even under high traffic

💡 Bonus Tip:
Always analyze your most frequent query patterns before adding composite indexes. Too many unused indexes can slow down writes and increase storage usage.

Use tools like:

  • EXPLAIN in PostgreSQL
  • EXPLAIN ANALYZE in MySQL
  • Query Analyzer in SQL Server To verify if your indexes are actually used by the optimizer.

If you found these SQL join optimization techniques helpful, please give this post a like! For a deeper dive into each technique, including real-world query fixes and more examples, I invite you to read the full article on Medium: 10 SQL Join Optimization Techniques Every Backend Developer Should Know. Don't forget to subscribe to my Medium for more backend development insights!

If you enjoyed this article, don’t forget to like ❤️, share 🔄, and subscribe 🔔 so you never miss future updates!

Top comments (0)