How to Create an Index in MySQL

Index names must be unique within the table, and the idx_table_column convention keeps them readable in EXPLAIN output. Indexing a low-cardinality column such as a two-value status flag is often not worth it, since the optimizer prefers a full scan when a large fraction of rows match anyway.

CREATE INDEX idx_orders_status ON orders (status);

Composite index

A composite index also serves queries filtering on a leading subset of its columns, so this one covers customer_id alone as well as customer_id plus created_at. It does not help a query filtering only on created_at. Putting the equality column first and the range column second lets MySQL narrow by customer before scanning the date range.

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

Check it is used

EXPLAIN shows the chosen index in the key column, or NULL when none is used. Check rows for how many the optimizer expects to examine, and look for Using index in Extra, which means the query was answered from the index alone without touching the table.

EXPLAIN SELECT * FROM orders
WHERE customer_id = 101 AND created_at >= '2026-01-01';