SQL Indexes

An index is a sorted lookup structure that lets the database find rows without reading the whole table, like the index at the back of a book. The right index turns a query that scans millions of rows into one that reads a handful. The wrong ones waste disk space and slow down every write. This lesson shows how to create indexes and, with EXPLAIN, how to prove MySQL uses them.

How an index works

Without an index, WHERE city = 'Pune' makes MySQL read every row and test it: a full table scan. An index on city keeps the city values sorted, each with a pointer to its row, in a balanced tree (B-tree). MySQL walks down the tree to the first ‘Pune’ and reads only the matching entries.

InnoDB always has one index already: the primary key, which also stores the rows themselves in key order.

See the difference with EXPLAIN

EXPLAIN shows how MySQL plans to run a query (EXPLAIN lesson). Look at type, key and rows:

EXPLAIN SELECT first_name FROM employees WHERE city = 'Pune';

CREATE INDEX

CREATE INDEX idx_emp_city ON employees (city);EXPLAIN SELECT first_name FROM employees WHERE city = 'Pune';

List and drop indexes:

CREATE INDEX idx_emp_city ON employees (city);SELECT index_name, column_name, non_uniqueFROM information_schema.statisticsWHERE table_schema = 'company' AND table_name = 'employees'ORDER BY index_name, seq_in_index;

DROP INDEX idx_emp_city ON employees; removes it. The foreign key on dept_id created its own index automatically.

Unique indexes

A unique index also enforces that no two rows share a value (UNIQUE):

CREATE UNIQUE INDEX uq_customer_email ON customers (email);

Composite indexes and the leftmost prefix

An index can cover several columns. Its entries are sorted by the first column, then the second, like a phone book sorted by last name, then first name:

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);EXPLAIN SELECT order_id FROM orders WHERE customer_id = 1 AND order_date >= '2025-02-01';

The index can be used for the leftmost columns:

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);EXPLAIN SELECT order_id, status FROM orders WHERE order_date >= '2025-02-01';

When an index cannot be used

CREATE INDEX idx_emp_hire ON employees (hire_date);EXPLAIN SELECT first_name FROM employees WHERE YEAR(hire_date) = 2022;EXPLAIN SELECT first_name FROM employees WHERE hire_date >= '2022-01-01' AND hire_date < '2023-01-01';

Covering indexes

If an index contains every column the query needs, MySQL answers from the index alone without touching the table. EXPLAIN then shows Using index in Extra:

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 1;

The cost of indexes

• Every INSERT, UPDATE and DELETE must also update every index on the table.
• Indexes take disk space and memory.
• So: index the columns used in WHERE, JOIN ... ON and ORDER BY of frequent queries, and drop indexes nobody uses (sys.schema_unused_indexes lists them).

Index FAQ