MySQL Index
A MySQL index is a structure that helps the database find rows without scanning the whole table. Without an index, a query with a condition on a column has to read every row. With an index, the database can jump to the relevant range.
When an index helps
Indexes are most useful on columns used in WHERE, JOIN, and ORDER BY. Unique values such as email or slug usually fit. Columns with little variation, such as active status or gender, often do not help much because the database still has to read many rows.
Indexes also help foreign keys and date-range queries. But an index does not automatically speed up every query. If a query applies a function to a column, such as WHERE YEAR(created_at) = 2026, the index on created_at may not be used. Rewrite it as a date range so the index stays effective.
Composite indexes and column order
A composite index covers several columns. Column order matters because MySQL uses the leftmost prefix rule. An index on (user_id, created_at) can serve a query that filters user_id, or both user_id and created_at. It cannot be used optimally for a query that filters only created_at.
Put the most selective column first, then the column used for sorting. For WHERE user_id = ? ORDER BY created_at DESC, an index on (user_id, created_at) is often better than two separate indexes.
How to check
Use EXPLAIN before and after adding an index. Look at the type, key, rows, and Extra columns. Using filesort or Using temporary can signal that the index does not support the query yet. SHOW INDEX FROM table_name; lists the existing indexes.
Enable the slow query log to find queries that are actually slow. Do not add an index just because a query looks complicated. Measure execution time and the number of rows examined.
What to watch for
Every index adds write cost. INSERT, UPDATE, and DELETE must update the index, so too many indexes can slow the application. Indexes also use disk and memory.
An index that is never used still adds overhead. Audit them regularly and remove duplicate indexes. For database context, see MySQL, phpMyAdmin, Connection Pooling, and NVMe SSD.
FAQ
Does every column need an index?
No. An index on a column that is rarely filtered or has low variation is often useless. Focus on slow queries that run often.
What is the leftmost prefix?
MySQL uses the leftmost columns of a composite index first. An index on (a, b) can serve a filter on a, but it is not optimal for a filter on b alone.
Why is a query still slow after adding an index?
Check whether the index is actually used in EXPLAIN. A function on the column, a type mismatch, or the wrong column order can make the index useless.
Disclaimer: Hosting Wiki articles are prepared for educational and reference purposes. Hosting technology keeps evolving, so some technical details may change over time.