Log in Sign up

What an index does

Without an index, MySQL has to read every row of a table to find matches (a full table scan). An index is a sorted structure (a B-tree in InnoDB) that lets MySQL jump straight to the matching rows – like the index at the back of a book.

Indexes speed up reading but make every INSERT, UPDATE and DELETE a little slower and use disk space. Index what you search for, not everything.

Creating and removing indexes

CREATE INDEX idx_status ON orders (status);
CREATE UNIQUE INDEX uq_email ON customers (email);
CREATE INDEX idx_customer_date ON orders (customer_id, created_at);

SHOW INDEX FROM orders;
DROP INDEX idx_status ON orders;

The PRIMARY KEY is always an index. In InnoDB the table data itself is stored in primary-key order.

Which columns to index

Good candidates:

  • columns in WHERE conditions that select few rows (email, customer_id)
  • columns used in JOIN ... ON
  • foreign-key columns (InnoDB creates these automatically)
  • columns in ORDER BY combined with LIMIT

Poor candidates:

  • columns with only a few different values on their own (is_active, status) – often a full scan is just as fast
  • small tables (a few thousand rows)

Composite indexes and the leftmost prefix

An index on (customer_id, created_at) can be used for:

Query conditionUses the index?
customer_id = 5yes
customer_id = 5 AND created_at > '2026-01-01'yes, fully
customer_id = 5 ORDER BY created_atyes, also for sorting
created_at > '2026-01-01' onlyno – the leftmost column is missing

Put columns tested with = first and range conditions (>, BETWEEN, LIKE 'abc%') last.

Things that prevent index use

WHERE YEAR(created_at) = 2026            -- function on the column: no index
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01'         -- same result, uses the index

WHERE email LIKE '%@example.com'         -- leading wildcard: no index
WHERE customer_id = '5'                  -- fine; but comparing a VARCHAR column with a number is not

EXPLAIN – how MySQL runs a query

Put EXPLAIN in front of a SELECT:

EXPLAIN SELECT * FROM orders WHERE customer_id = 5 ORDER BY created_at DESC;

The important columns:

ColumnWhat to look for
typeALL = full table scan (bad on big tables); ref, range, eq_ref, const are good
possible_keysindexes MySQL could use
keyindex it actually chose (NULL = none)
rowsestimated rows to examine – smaller is better
ExtraUsing filesort or Using temporary = extra work; Using index = answered from the index alone

To actually run the query and see real timings:

EXPLAIN ANALYZE SELECT ...;     -- MySQL 8.0.18+
ANALYZE SELECT ...;             -- MariaDB

Finding slow queries

Turn on the slow query log in the server configuration (see MySQL Configuration, Logs and Troubleshooting):

[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 1          # seconds

Or temporarily at runtime:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

Summarise the log:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log    # top 10 by total time

pt-query-digest from Percona Toolkit gives even more detailed reports.

Other quick wins

  • Select only the columns you need instead of SELECT *.
  • Use LIMIT for lists and pagination.
  • Insert many rows in one statement or one transaction.
  • Keep statistics fresh on tables that changed a lot: ANALYZE TABLE orders;
  • Make sure innodb_buffer_pool_size is large enough to hold the hot data in RAM.

For searching words in long text, a FULLTEXT index is much faster than LIKE '%word%':

ALTER TABLE articles ADD FULLTEXT INDEX ft_body (title, body);
SELECT id, title FROM articles
WHERE MATCH(title, body) AGAINST ('backup restore' IN NATURAL LANGUAGE MODE);

Next: MySQL Transactions and InnoDB

Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.