An index is a data structure that lets MySQL locate rows quickly based on column values, accelerating lookups, range filters, joins, and sorted access. By narrowing the rows MySQL must examine, an index can transform a multi-second scan into a millisecond lookup. However, indexes are not free: every insert, update, and delete must maintain them, and each index consumes storage. As a result, an over-indexed table pays a recurring tax on writes, and a long-term indexing strategy must balance read speed against write cost and storage overhead. Small tables are often cheaper to scan than to maintain multiple indexes, so over-indexing very small tables adds write overhead without real read benefit.
Composite indexes—those spanning multiple columns—follow a leftmost prefix rule: MySQL can use an index on (a, b, c) to filter or sort on (a), (a, b), or (a, b, c) in that order, but it cannot skip columns in the middle. This makes the column order of a composite index critical, and queries that filter on later columns without the earlier ones will not benefit. A particularly powerful technique is the covering index, which includes every column the query needs; when this happens, MySQL can satisfy the query entirely from index pages without touching table rows, dramatically reducing I/O. For example, a query filtering by user_id and selecting user_id and created_at can be served from a single index on (user_id, created_at). Cardinality—the number of distinct values—also matters: highly selective indexes quickly narrow down to few rows, while low-selectivity indexes such as a boolean flag on its own may be ignored by the optimizer in favor of a table scan and only become useful when combined with more selective columns in a composite key. NULLable columns can reduce index selectivity and complicate cardinality estimates, so they should be avoided in composite indexes where possible.
Index hints such as USE INDEX or FORCE INDEX exist to steer the optimizer, and the optimizer_switch setting controls various optimizer behaviors, but hints should be used sparingly because they can lock in suboptimal plans if data distributions change. Invisible indexes offer a safer alternative: the index is still maintained but ignored by the optimizer unless explicitly referenced, allowing you to simulate dropping an index without losing it. Index management is an ongoing practice. Reactive, query-by-query index additions tend to accumulate redundant or overlapping indexes that hurt write performance without proportional read benefit. Periodic reviews using performance_schema and sys schema views can reveal indexes that are never used and can be safely dropped, and any new index should be evaluated alongside the WHERE, JOIN, ORDER BY, and GROUP BY clauses of real queries. Indexing foreign key columns is particularly important because unindexed foreign keys cause full scans on parent and child tables during referential checks. Over time, indexes also fragment due to page splits and deletions; operations like OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB can rebuild them, though such operations should be scheduled carefully because they can lock large tables and require extra space.