An index is a data structure that gives MySQL fast access paths to rows based on column values, dramatically reducing the number of rows the server must examine to satisfy a query. By providing ordered or hashed lookups, indexes accelerate filters, joins, and ORDER BY operations. The trade-off is well known: every additional index speeds up reads but slows down writes, because inserts, updates, and deletes must maintain all the indexes on a table, and each index consumes storage and buffer pool memory.
Composite (multi-column) indexes are especially powerful when queries consistently filter or sort on the same set of columns. The leftmost prefix rule states that MySQL can use a composite index for predicates that start from its leftmost column and continue in order. For an index on (a, b, c), effective filter combinations are (a), (a, b), and (a, b, c); a query that only filters on (b) or (c) cannot benefit. The order of columns in a composite index therefore should reflect the most selective and most frequently used prefix, while aligning with ORDER BY and GROUP BY clauses when possible.
Two important refinements are covering indexes and selectivity. A covering index contains all the columns a query needs so that MySQL can satisfy the query from index pages alone, without touching the underlying table rows. For example, an index on (user_id, created_at) can cover a query that filters by user_id and selects those two columns, enabling an index-only scan. Index usefulness, however, depends heavily on selectivity: an index is most valuable when it filters out most rows. Highly selective indexes match a small fraction of rows, while low-selectivity columns like boolean flags are often better combined with more selective columns in a composite index rather than indexed alone. Other nuances include indexing foreign key columns to avoid full scans on parent/child checks, using prefix indexes on very long string columns to keep size manageable, and being aware of cardinality so the optimizer can make accurate choices.
Index maintenance is part of the discipline. Indexes that accumulate without ongoing review waste resources and slow writes. Invisible indexes in MySQL 8 are a useful tool: they remain maintained on disk but are ignored by the optimizer, so you can simulate dropping an index safely before removing it. Periodically reviewing index usage through information_schema and performance_schema, dropping redundant or unused indexes, and revisiting composite indexes as query patterns evolve all keep the indexing strategy healthy over time.