SQL Index Optimization: Speed Up Slow Queries (Real Examples)

Why Slow Queries Happen

When you run a SELECT without a supporting index, the database performs a full table scan: it reads every row, evaluates the WHERE condition, and discards the rows that do not match. On a table with 10,000 rows this is fast. On a table with 10 million rows, a full scan can take seconds or even minutes - even on fast NVMe storage.

An index is a separate data structure (typically a B-tree) that stores a sorted copy of one or more column values alongside pointers to the full rows. Instead of scanning the whole table, the database navigates the B-tree in O(log n) time and jumps directly to the matching rows. On a million-row table, the difference between a full scan and an index lookup can be three orders of magnitude.

How B-Tree Indexes Work

The default index type in MySQL, PostgreSQL, and most relational databases is the B-tree (Balanced tree). A B-tree index keeps values sorted, which supports:

• Exact lookups: WHERE email = 'alice@example.com'
• Range scans: WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31'
• Prefix searches: WHERE last_name LIKE 'Smith%'
• Sorted output: ORDER BY created_at DESC can use the index directly without a sort step

B-trees do not support suffix searches (LIKE '%smith') or arbitrary function calls on indexed columns (WHERE YEAR(created_at) = 2026). For those cases, use full-text indexes, generated columns, or expression indexes (PostgreSQL).

Hash indexes (available in PostgreSQL and MySQL MEMORY tables) support only exact equality lookups and are faster for that case but cannot support range or sort operations. In most OLTP workloads, B-tree is the right default.

When to Add an Index

Add an index when the column appears in:

• WHERE clauses that filter to a small fraction of rows (high selectivity)
• JOIN conditions: foreign key columns are almost always worth indexing
• ORDER BY / GROUP BY columns, to avoid a filesort step
• Columns used in aggregations (MIN, MAX, COUNT) that benefit from index-only scans

When NOT to Add an Index

• Small tables (< 1,000 rows): The optimizer may choose a full scan anyway; the index overhead is not worth it.
• Low-cardinality columns: A boolean is_active column with only two distinct values offers no meaningful filtering. The index would return 50% of rows, at which point a full scan is equally efficient.
• Write-heavy tables: Every INSERT, UPDATE, and DELETE must update all indexes on the table. If a table is updated thousands of times per second, excessive indexes degrade write throughput significantly.
• Columns never used in WHERE, JOIN, or ORDER BY: Unused indexes waste storage and slow writes with no benefit.

Composite Indexes: Column Order Matters

A composite index spans multiple columns. The key insight is that the leftmost prefix rule governs which queries can use it. A composite index on (status, created_at, user_id) can satisfy queries that filter on:

• status alone
• status and created_at
• status, created_at, and user_id

It cannot efficiently satisfy a query filtering only on created_at or only on user_id. The database cannot skip to the middle of the index.

-- This composite index:
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- Helps this query (uses both columns):
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2026-01-01';

-- Also helps this (leftmost prefix):
SELECT * FROM orders WHERE status = 'shipped';

-- Does NOT help this (skips the leftmost column):
SELECT * FROM orders WHERE created_at > '2026-01-01';

Rule of thumb for composite index column order: Put the most selective column first (the one that eliminates the most rows), then additional columns in order of selectivity. Columns used in equality conditions should come before columns used in range conditions.

Covering Indexes: Eliminate Table Lookups

A covering index contains all the columns that a query needs, so the database can answer the query entirely from the index without touching the main table. This is the fastest possible index scenario.

-- Query
SELECT user_id, created_at FROM orders WHERE status = 'pending';

-- Covering index: includes all queried columns
CREATE INDEX idx_orders_covering ON orders (status, user_id, created_at);

-- MySQL EXPLAIN shows "Using index" in Extra column -- no table lookup needed
-- PostgreSQL EXPLAIN shows "Index Only Scan"

The tradeoff is that covering indexes are larger (they store more data per row). Add the extra columns only if the query is on the critical path and the table is large enough to justify it.

Reading EXPLAIN Output

Before and after adding indexes, use EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) to understand the query plan:

-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Key columns to watch:
-- type:  ALL = full scan (bad), ref/range = index (good), eq_ref/const = optimal
-- key:   which index was chosen (NULL = no index used)
-- rows:  estimated row count scanned (lower is better)
-- Extra: "Using index" = covering, "Using filesort" = sort not covered by index

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Look for:
-- Seq Scan = full table scan
-- Index Scan = index used but fetches table rows
-- Index Only Scan = covering index, fastest
-- actual time = real execution time (only with ANALYZE)

Step-by-Step: Adding an Index in Production

1. Identify the slow query: Use slow query log (SET GLOBAL slow_query_log = 'ON'), pg_stat_statements, or your APM tool to find the worst offenders.
2. Run EXPLAIN: Identify whether the plan shows a full scan (type: ALL in MySQL, Seq Scan in PostgreSQL) and which columns are in the WHERE/JOIN/ORDER BY.
3. Design the index: Apply the leftmost prefix rule and consider whether a covering index is warranted.
4. Create the index concurrently: On live tables, use CREATE INDEX CONCURRENTLY (PostgreSQL) or ALGORITHM=INPLACE (MySQL 5.6+) to avoid locking the table.
5. Verify the plan changed: Run EXPLAIN again and confirm the new index is being used.
6. Monitor write performance: Check that INSERT/UPDATE throughput has not degraded significantly after adding the index.

-- PostgreSQL: non-blocking index creation
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status)
WHERE status != 'archived';  -- Partial index: smaller and faster

-- MySQL: online DDL
ALTER TABLE orders
ADD INDEX idx_orders_user_status (user_id, status),
ALGORITHM=INPLACE, LOCK=NONE;

Partial Indexes: Index Only What You Query

A partial index (PostgreSQL) or filtered index (SQL Server) includes only rows matching a condition. This makes the index much smaller when you only ever query a subset of rows:

-- Only index pending orders (active subset, not the full history)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- Only index non-null values
CREATE INDEX idx_users_phone ON users (phone)
WHERE phone IS NOT NULL;

A partial index for status = 'pending' on an orders table might be 100x smaller than a full index on the same column, because completed orders vastly outnumber pending ones. Smaller index = faster lookups, less RAM usage, faster writes.

Common Indexing Mistakes

• Function on indexed column: WHERE LOWER(email) = 'alice@example.com' cannot use a regular index on email. Create a functional/expression index: CREATE INDEX idx_users_email_lower ON users (LOWER(email)).
• Leading wildcard in LIKE: WHERE name LIKE '%smith' cannot use a B-tree index. Either reverse the string and index it, or use a full-text search index.
• Implicit type conversion: WHERE user_id = '42' where user_id is an integer forces the database to cast every row, defeating the index. Always match data types.
• OR without a union: WHERE a = 1 OR b = 2 often results in a full scan. Rewrite as SELECT ... WHERE a = 1 UNION ALL SELECT ... WHERE b = 2 if both columns are indexed.
• Too many indexes on write-heavy tables: A table with 15 indexes on a high-volume event stream will have significant write overhead. Profile before adding.
• Duplicate indexes: An index on (a) is redundant if you already have one on (a, b). The composite index satisfies queries on just a via the leftmost prefix rule.

Use our free tool here → SQL Formatter

Frequently Asked Questions

How many indexes should a table have?

There is no fixed limit, but a practical guideline for OLTP tables is 5–8 indexes maximum. Each index adds overhead to every write operation and consumes storage. Profile your actual queries, add indexes only for queries that are on the critical path, and periodically review for unused indexes (pg_stat_user_indexes in PostgreSQL, sys.dm_db_index_usage_stats in SQL Server).

Does adding an index always make a query faster?

No. The query optimizer may choose not to use an index if the estimated row count is too high (low selectivity), if the table is small enough that a full scan is cheaper, or if the statistics are stale. After adding an index, run ANALYZE TABLE (MySQL) or ANALYZE (PostgreSQL) to update statistics, then verify with EXPLAIN that the index is being used.

What is a covering index and when should I use one?

A covering index includes all columns referenced in the query (in SELECT, WHERE, JOIN, and ORDER BY). When the database can satisfy the entire query from the index without touching the main table, it performs an index-only scan, which is significantly faster. Use covering indexes for your most critical read queries, especially on tables with many columns where the rows are large.

How do I find unused indexes in PostgreSQL?

Query pg_stat_user_indexes: SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND schemaname = 'public'. Indexes with zero scans since the last stats reset are candidates for removal. Reset stats with SELECT pg_stat_reset() and let the system run for a representative period (at least one full business cycle) before making decisions.

Should I index foreign key columns?

In most cases yes. PostgreSQL does not automatically create indexes on foreign key columns (unlike MySQL which creates them with the FK constraint). Unindexed foreign keys cause full scans on JOIN operations and significantly slow down DELETE operations on the parent table (which must check for child rows). Always explicitly create an index on foreign key columns unless the table is very small or write performance is the critical concern.