Database Indexing Strategies for MySQL and PostgreSQL

A client's WooCommerce store had a "recently viewed" widget that took 4.2 seconds to render once they crossed 200,000 rows in wp_postmeta. No plugin was at fault; the query was fine; it was simply doing a full scan. One composite index took it to 18 milliseconds. Indexing is the highest-leverage skill in backend performance, and it is mostly one mental model plus a habit of reading query plans.

The mental model: a sorted phone book

A B-tree index on (last_name, first_name) is a phone book: sorted by last name, then first name, with each entry pointing at the full row. Finding "Khan, Shawab" is a handful of page reads regardless of how many entries exist (O(log n)). Finding everyone whose first name is "Shawab" means reading the whole book, because the book is not sorted by first name. That single observation explains most indexing rules:

• Leftmost prefix: an index on (a, b, c) serves queries on a, (a, b) and (a, b, c), but not on b alone.
• Ranges stop the search: once the query uses a range on a column (>, BETWEEN, LIKE 'abc%'), later index columns only help for sorting and covering, not for narrowing.
• Functions hide the column: WHERE LOWER(email) = ? cannot use an index on email; index the expression instead or store a normalised column.
• Leading wildcards are full scans: LIKE '%khan' cannot seek. Use full-text search or a trigram index.

The WordPress case: 4.2 s to 18 ms

The widget's query, simplified:

SELECT post_id
FROM wp_postmeta
WHERE meta_key = '_umm_last_viewed'
  AND meta_value > '2026-05-01 00:00:00'
ORDER BY meta_value DESC
LIMIT 12;

WordPress ships wp_postmeta with an index on meta_key(191) and one on post_id. The meta_key index finds all 200k rows with that key quickly, but then MySQL has to read each row to check meta_value, sort them all, and keep 12. EXPLAIN showed Using where; Using filesort over 198,000 rows.

-- Composite index: equality column first, then the range/sort column.
-- meta_value is LONGTEXT, so we index a prefix; 32 chars covers a datetime string.
ALTER TABLE wp_postmeta ADD INDEX umm_key_value (meta_key(191), meta_value(32));

-- After: EXPLAIN shows "Using index condition", rows examined: 12, no filesort.

Result: 4.2 s → 18 ms. The general lesson is that WordPress' default indexes are designed for its own queries; any plugin or theme that filters on meta_value needs its own composite index, or better, a custom table. I add these in a mu-plugin on activation so they survive core updates.

Composite index column order

The rule of thumb for a query with equality conditions, one range condition and an ORDER BY:

1. Equality columns (most selective first if you have to choose)
2. The single range column
3. Columns from ORDER BY (only helps if the range column is also the sort column, or there is no range)
4. Extra columns the query reads, to make it covering

-- Query: recent paid orders for a customer, newest first, showing totals
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = $1 AND status = 'paid' AND created_at > now() - interval '90 days'
ORDER BY created_at DESC LIMIT 20;

-- Good: equality (customer_id, status), then range+sort (created_at), then INCLUDE for covering (Postgres 11+)
CREATE INDEX orders_cust_status_created_idx
  ON orders (customer_id, status, created_at DESC)
  INCLUDE (id, total_cents);

-- MySQL has no INCLUDE; add the columns to the key instead:
-- ALTER TABLE orders ADD INDEX (customer_id, status, created_at, id, total_cents);

Wrong order, (created_at, customer_id, status), forces a range scan over every order in the last 90 days across all customers, then filters. Same columns, 50x slower. Column order is not cosmetic.

Covering indexes and index-only scans

If every column the query touches is in the index, the database never reads the table. Postgres calls this an Index Only Scan, MySQL shows Using index. For a hot query on a wide table this can be a 10x win by itself, because heap pages are large and index pages are dense. The INCLUDE above is exactly this: id and total_cents are stored in the leaf but not part of the sort key, keeping the index compact.

Reading EXPLAIN ANALYZE

The plan is the truth. What I look for, in order:

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

-- Things that mean "needs an index":
--   Seq Scan on orders  (rows=1,203,441)        full table scan on a big table
--   Sort  (Sort Method: external merge Disk)    sorting spilled to disk
--   Rows Removed by Filter: 998,213             index found rows, then threw most away → wrong/partial index
--   Buffers: shared read=45,000                 lots of disk reads

-- Things that mean "good":
--   Index Only Scan using orders_cust_status_created_idx   covering index in use
--   Heap Fetches: 0                              never touched the table
--   actual time=0.04..0.21                       sub-millisecond

-- MySQL
EXPLAIN ANALYZE SELECT ... ;      -- 8.0.18+; older: EXPLAIN FORMAT=JSON
--   type: ALL              full scan (bad)     type: ref / range (good)
--   Extra: Using filesort  sort without index  Extra: Using index (covering)

One habit: keep a slow-queries.sql file in the repo with the top queries and their plans before and after each index change. It doubles as documentation for the next developer.

Beyond B-tree: the other index types you actually need

-- Partial index: the "pending orders" dashboard query only ever looks at pending rows.
-- 2% of the table, so the index is tiny and always in memory.
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

-- Expression index for case-insensitive email lookups
CREATE UNIQUE INDEX users_email_lower_idx ON users (lower(email));
-- Query must use the same expression: WHERE lower(email) = lower($1)

Indexes are not free

Every index is another structure to update on INSERT, UPDATE and DELETE, and more pages competing for RAM. A table with twelve indexes can insert 3-5x slower than one with three. Find and drop the dead weight:

-- PostgreSQL: indexes never used since stats reset (check after a representative period!)
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL (performance_schema / sys)
SELECT * FROM sys.schema_unused_indexes;

Also watch for redundant indexes: (a) is redundant if (a, b) exists, since the composite serves the same leftmost queries.

A repeatable process

1. Find the slow queries. pg_stat_statements or the MySQL slow query log with a 100 ms threshold. Sort by total time, not by max time; a 20 ms query run 50,000 times a day matters more than a 2 s report run once.
2. EXPLAIN ANALYZE the top five. Look for sequential scans and filesorts on large row counts.
3. Design one index per query shape using the column-order rule. Consider covering and partial variants.
4. Create it concurrently in production (CREATE INDEX CONCURRENTLY in Postgres; ALGORITHM=INPLACE, LOCK=NONE in MySQL) so you do not lock the table.
5. Re-EXPLAIN and record the before/after in the repo.
6. Review unused indexes monthly.

Indexing is the first thing I check on any performance engagement, before caching, before hardware, before rewriting anything. It is also the part most likely to give you a 100x improvement for ten minutes of work. For the layer above the database, see keyset pagination in the REST API guide, which only works because of the index rules described here.