Database

Design Database Indexes Around Real Queries

Use query patterns, selectivity and execution plans to create indexes that improve reads without imposing unnecessary write costs.

Overview

An index is not automatically beneficial because a column appears in a WHERE clause. Index design should begin with the queries the application actually runs, the size and distribution of the data, and the execution plan.

Start with representative queries

Capture frequent and expensive SQL statements. Record their filters, joins, sort order and expected result size. Then inspect the plan with EXPLAIN. Look for full table scans on large tables, poor row estimates, temporary tables and filesorts, but interpret each signal in context.

Match composite index order

For a query such as:

SELECT id, title, published_at
FROM articles
WHERE status = 'published' AND category_id = ?
ORDER BY published_at DESC, id DESC
LIMIT 20;

A candidate index is:

CREATE INDEX idx_articles_status_category_date
ON articles (status, category_id, published_at, id);

The best order depends on selectivity, equality predicates, range predicates and the database optimizer. Test candidate indexes with realistic data rather than copying an index recipe blindly.

Avoid redundant indexes

A composite index can sometimes serve queries that use its leftmost prefix. Separate indexes may still be useful, but every additional index consumes disk space and increases insert, update and delete work. Check existing indexes before adding another one.

Measure before and after

Use the same representative dataset and query parameters. Compare execution plans and timings under comparable conditions. A query that returns a small fraction of rows may benefit from an index; a query that reads most of a table may be faster with a scan.

Production cautions

Large index creation can consume I/O and affect availability. Check the database version and supported online DDL behavior before applying changes. Schedule risky operations, monitor replication or backups where relevant, and keep a rollback plan.

Checklist

  • Start from real slow or frequent queries.
  • Inspect EXPLAIN output.
  • Consider composite index column order.
  • Check for redundant and unused indexes.
  • Measure read improvement and write overhead.
  • Plan production DDL around table size and engine support.

References

MySQL 8.4: Optimization and Indexes · MariaDB Indexes

Share this page Help other developers discover this resource.