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
WHEREconditions that select few rows (email,customer_id) - columns used in
JOIN ... ON - foreign-key columns (InnoDB creates these automatically)
- columns in
ORDER BYcombined withLIMIT
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 condition | Uses the index? |
|---|---|
customer_id = 5 | yes |
customer_id = 5 AND created_at > '2026-01-01' | yes, fully |
customer_id = 5 ORDER BY created_at | yes, also for sorting |
created_at > '2026-01-01' only | no – 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 notEXPLAIN – 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:
| Column | What to look for |
|---|---|
type | ALL = full table scan (bad on big tables); ref, range, eq_ref, const are good |
possible_keys | indexes MySQL could use |
key | index it actually chose (NULL = none) |
rows | estimated rows to examine – smaller is better |
Extra | Using 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 ...; -- MariaDBFinding 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 # secondsOr 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 timept-query-digest from Percona Toolkit gives even more detailed reports.
Other quick wins
- Select only the columns you need instead of
SELECT *. - Use
LIMITfor 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_sizeis large enough to hold the hot data in RAM.
Full-text search
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);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.