How to Choose Indexes for Common SQL Queries

This guide walks you through choosing effective indexes for common SQL query patterns in SQL Server, MySQL, and PostgreSQL. You’ll learn the rules for single-column, composite, covering, and filtered indexes, and how to verify your choices with each database’s query planner. No prior indexing knowledge is required, but basic familiarity with SELECT, WHERE, JOIN, and ORDER BY helps.

What You’ll Need

Access to a database instance (SQL Server, MySQL, or PostgreSQL) where you can run EXPLAIN and CREATE INDEX.
A sample table with at least a few thousand rows to test index effectiveness.
Your actual query or a realistic version of it.
Permission to create indexes (or a development copy of the database).

Step-by-Step

Identify the slow query. Use the database’s slow query log or profiling tools. In SQL Server, use Query Store; in MySQL, enable slow_query_log; in PostgreSQL, set log_min_duration_statement. Capture the exact SQL text.
Run EXPLAIN to see the current plan. In SQL Server: SET SHOWPLAN_ALL ON or use the graphical plan in SSMS. In MySQL: EXPLAIN SELECT .... In PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) SELECT .... Look for full table scans (SQL Server: ‘Table Scan’, MySQL: ‘type=ALL’, PostgreSQL: ‘Seq Scan’).
Check the WHERE clause for equality conditions. For each column compared with =, create a single-column index. Example: CREATE INDEX idx_status ON orders (status); This works in all three databases.
Add indexes for range conditions. For >, <, BETWEEN, or LIKE 'abc%', index the column. Range scans use the index to find the starting point, then scan forward. Example: CREATE INDEX idx_created ON orders (created_at);
Combine equality and range into a composite index. Put equality columns first, then range columns. For WHERE status = 'paid' AND created_at > '2024-01-01', create CREATE INDEX idx_status_created ON orders (status, created_at); This is crucial in all three databases.
Index columns used in JOINs. For JOIN customers ON orders.customer_id = customers.id, ensure there’s an index on orders.customer_id (foreign key). Most databases auto-index primary keys, but not foreign keys. Add CREATE INDEX idx_customer ON orders (customer_id);
Cover the SELECT list with a covering index. If the query only needs a few columns, include them in the index after the WHERE columns. For SELECT status, created_at FROM orders WHERE status = 'paid', create CREATE INDEX idx_status_created_covering ON orders (status, created_at); This lets the index satisfy the whole query without touching the table.
For ORDER BY, match the index order. If you sort by created_at DESC, an index on created_at helps. For multiple sort keys, the index must match the sort order and direction. Example: CREATE INDEX idx_created_desc ON orders (created_at DESC); (MySQL and PostgreSQL support DESC; SQL Server does too).
Use filtered/partial indexes for sparse data. In SQL Server: CREATE INDEX idx_active ON orders (status) WHERE status = 'active'; In PostgreSQL: CREATE INDEX idx_active ON orders (status) WHERE status = 'active'; MySQL does not support filtered indexes directly; use a generated column or just a regular index.
Test the index with EXPLAIN again. Re-run the EXPLAIN and confirm the scan type changed to an index seek/range scan. In SQL Server look for ‘Index Seek’, in MySQL ‘type=ref’ or ‘range’, in PostgreSQL ‘Index Scan’ or ‘Bitmap Index Scan’.
Measure real performance. Run the query with and without the index (drop it temporarily) and compare execution time. Use a realistic dataset size.
Monitor for over-indexing. Each index slows down INSERT/UPDATE/DELETE. Keep only indexes that are actually used. Use the database’s index usage stats: SQL Server sys.dm_db_index_usage_stats, MySQL performance_schema.table_io_waits_summary_by_index_usage, PostgreSQL pg_stat_user_indexes.

Common Mistakes

Indexing every column. An index on a low-cardinality column like gender rarely helps; the optimizer may ignore it.
Wrong column order in composite indexes. Putting the range column first makes the equality column useless after the range.
Using functions on indexed columns. WHERE YEAR(created_at) = 2024 prevents index usage. Use created_at >= '2024-01-01' AND created_at < '2025-01-01' instead.
Ignoring leading wildcards. LIKE '%abc' cannot use an index; only 'abc%' can.
Overlooking foreign key indexes. JOINs become full table scans without them.
Creating duplicate indexes. CREATE INDEX idx_a ON t(a) and CREATE INDEX idx_a_b ON t(a,b) are redundant for queries that only use a.

Tips

Start with the most critical queries (heavy reads, frequent execution) and index those first.
Use the query planner’s recommendations: SQL Server’s Database Tuning Advisor, MySQL’s EXPLAIN output, PostgreSQL’s pg_hint_plan or auto_explain.
For composite indexes, keep the total index size reasonable—don’t include large text or BLOB columns.
In PostgreSQL, consider INCLUDE columns for covering indexes: CREATE INDEX idx ON t (a) INCLUDE (b, c); This reduces index size.
In MySQL, prefix indexes can help on long strings: CREATE INDEX idx_email ON users (email(10)); But be careful with uniqueness.
Regularly review and drop unused indexes—use the stats mentioned in Step 12.

Troubleshooting

Index not being used. Check for functions or implicit type conversions on the column. Also, if the table is small (a few hundred rows), the optimizer may prefer a full scan. Force it temporarily with hints (SQL Server: WITH (INDEX(idx)), MySQL: USE INDEX(idx), PostgreSQL: SET enable_seqscan = off;) to see if the index helps.

Index seek still slow. The index may be too wide, causing many page reads. Try a covering index or reduce the number of columns.

Composite index not helping. Verify the column order matches the query’s equality/range structure. Also, check if the query uses an OR condition—OR often defeats index usage unless it’s a UNION.

Insert performance dropped. Too many indexes. Drop any that are not used by SELECT queries.

Different results on different databases. Each optimizer has its own cost model. Test the same index design in each database separately; what works in PostgreSQL may not work in MySQL.

Frequently Asked Questions

Q: Should I index every column in the WHERE clause?
A: Not necessarily. If a column has few distinct values (like a boolean), a single-column index may be ignored. Combine it with other columns in a composite index.

Q: What’s the difference between a clustered and nonclustered index?
A: A clustered index determines the physical order of rows (only one per table, usually the primary key). Nonclustered indexes are separate structures that point to rows. In MySQL InnoDB, the primary key is clustered; in SQL Server you can choose; in PostgreSQL there’s no clustered concept (CLUSTER is a one-time command).

Q: Can I use the same index for multiple queries?
A: Yes, if the queries share the same leading columns and similar conditions. A composite index on (a,b) can serve queries with WHERE a=1 and WHERE a=1 AND b=2, but not WHERE b=2 alone.

Q: How do I know if an index is actually being used?
A: Use the database’s index usage statistics (see Step 12). If an index has zero reads after a week, it’s likely unused.

Q: Does index order matter for single-column indexes?
A: No, a single-column index is just a sorted list. But for composite indexes, order is critical—leftmost prefix rule applies in all three databases.

Q: What about indexes on columns with NULL values?
A: In SQL Server and PostgreSQL, NULLs are included in indexes and can be used for IS NULL conditions. In MySQL, NULLs are also indexed, but IS NULL may not use the index in some older versions. Test with EXPLAIN.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →