Most slow database queries aren’t caused by slow hardware — they’re caused by the database reading far more rows than it needs. An index lets it jump straight to the matching rows, turning a scan of millions of rows into a lookup of a few. Understanding a handful of principles fixes the majority of slow queries in MySQL and PostgreSQL.

What an index is

A standard index is a B-tree: a sorted structure of column values pointing to rows. Like a book’s index, it lets the database find “customer_email = ‘alice@…’” without reading every page. B-trees support:

  • equality (=, IN),
  • ranges (<, >, BETWEEN),
  • prefix matches (LIKE 'abc%'),
  • sorting (ORDER BY) in index order.

Spot the problem with EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_email = '[email protected]';

Warning signs:

  • MySQL: type: ALL (full table scan), large rows, Extra: Using filesort / Using temporary.
  • PostgreSQL: Seq Scan on a large table, high estimated rows; EXPLAIN ANALYZE shows actual time and rows.

See MySQL EXPLAIN and find candidates with the slow query log or pg_stat_statements.

Index the columns you filter and join on

CREATE INDEX idx_orders_email ON orders (customer_email);

Good candidates: columns in WHERE clauses, JOIN conditions (foreign keys!), and ORDER BY on large result sets.

PostgreSQL does not automatically index foreign key columns — joins and cascading deletes on unindexed foreign keys are a classic slowdown. MySQL/InnoDB creates them automatically.

Composite indexes: order matters

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

This index serves:

  • WHERE status = 'paid'
  • WHERE status = 'paid' AND created_at > '2026-10-01'
  • WHERE status = 'paid' ORDER BY created_at DESC

But not WHERE created_at > '2026-10-01' alone — the leftmost column must be used (the “leftmost prefix” rule). Rules of thumb:

  1. Equality conditions first, range conditions last.
  2. Put the column that’s always filtered first.
  3. Columns used for sorting after the equality columns, in the same direction.

Covering indexes

If an index contains every column a query needs, the database can answer from the index alone without touching the table:

-- query
SELECT created_at, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- covering index
CREATE INDEX idx_orders_cust_created_total ON orders (customer_id, created_at, total);
-- PostgreSQL alternative: CREATE INDEX … ON orders (customer_id, created_at) INCLUDE (total);

MySQL shows Using index; PostgreSQL shows Index Only Scan.

What prevents index use

  • Functions on the column: WHERE LOWER(email) = '…' or WHERE DATE(created_at) = '2026-11-04'. Rewrite as a range (created_at >= '2026-11-04' AND created_at < '2026-11-05') or create an expression/functional index.
  • Leading wildcards: LIKE '%keyboard%' can’t use a B-tree. Use full-text search (MySQL FULLTEXT, PostgreSQL tsvector, pg_trgm) or a search engine.
  • Type mismatches: comparing a string column to a number forces conversion.
  • Low selectivity: an index on a boolean or a column with three values rarely helps on its own (partial indexes can: CREATE INDEX … WHERE status = 'pending' in PostgreSQL).
  • OR across different columns: may need separate indexes or a UNION.

The cost of indexes

Every index:

  • slows down INSERT, UPDATE and DELETE (it must be maintained),
  • uses disk and memory (buffer pool / shared buffers),
  • needs to be considered by the planner.

Don’t index every column. Remove unused indexes:

-- PostgreSQL
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;
-- MySQL (sys schema)
SELECT * FROM sys.schema_unused_indexes;

And redundant ones: an index on (a) is redundant if you have (a, b).

Adding indexes on a live database

  • PostgreSQL: CREATE INDEX CONCURRENTLY avoids blocking writes.
  • MySQL 8 / InnoDB: most ADD INDEX operations are online (ALGORITHM=INPLACE, LOCK=NONE); for very large tables consider gh-ost or pt-online-schema-change.

A workflow

  1. Find the slowest/most frequent queries (slow log, pg_stat_statements).
  2. EXPLAIN them.
  3. Add a targeted index (composite if needed).
  4. EXPLAIN again and measure.
  5. Repeat; remove indexes nobody uses.

Related: SQL joins explained, MySQL performance tuning and PostgreSQL performance tuning.

  • Databases
  • Performance
  • SQL