What an index does
An index is an auxiliary data structure that helps MySQL find rows or produce an ordered result without examining every table row. InnoDB commonly uses B-tree indexes for primary and secondary access. Indexes can reduce work for selective filters, joins, and ordering, but they consume disk, memory, and maintenance effort. Every insert may add index entries, and updates or deletes may need to change them. The right index depends on the application's actual workload rather than a rule to index every column.
In InnoDB, the primary key is the clustered index that organizes the table's row data. A secondary index stores its indexed columns along with the primary-key value used to locate the row. This means a wide primary key is repeated in secondary indexes and can increase their size. Choose a stable primary key with an appropriate size, and avoid changing it unnecessarily. A secondary index may cover a query when it contains all required columns, but adding every output column can make the index costly.
Composite indexes and leftmost prefixes
A composite index contains multiple columns in a defined order. An index on (customer_id, created_at) can often support lookups beginning with customer_id and then created_at, but it does not generally provide the same access path for a condition using only created_at. This leftmost-prefix behavior makes column order important. Put equality conditions and common ordering needs into context, then consider selectivity and actual query patterns. A universal “most selective first” rule is too simplistic for every workload.
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);
SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;This index may help filter orders for one customer and supply rows in time order. Whether MySQL chooses it depends on statistics, table size, data distribution, and requested columns. Direction and descending-index behavior also depend on version and index definition. Confirm with EXPLAIN instead of assuming that matching column names guarantee a particular plan.
Index types and specialized workloads
Ordinary B-tree indexes are appropriate for many equality and range predicates. Unique indexes enforce uniqueness as well as support lookups. FULLTEXT indexes support token-based text search with their own parsing and relevance behavior; they are not interchangeable with substring LIKE searches. Spatial indexes support spatial data access. Hash indexes exist in particular MySQL contexts, such as adaptive or memory-engine behavior, but should not be confused with a general InnoDB index choice. Choose the index type according to the data and query semantics.
Common index mistakes
An index on a low-cardinality column may not help a query that returns most rows. A leading wildcard such as LIKE '%phone' generally prevents a conventional B-tree from seeking by a prefix. Applying a function or implicit conversion to the indexed column may also change index use, although functional indexes can be appropriate for selected cases and versions. Redundant overlapping indexes waste resources. Before adding an index, inspect existing keys and the query plan, and determine whether the query is truly slow relative to its business importance.
Unused indexes can accumulate as code changes. Removing one without observing production workloads can cause a regression for a rare but critical query. Use monitoring and safe deployment practices to evaluate candidate indexes, and account for online DDL behavior and lock impact when changing a large table. Indexes also affect replication volume and storage backups, so the cost is broader than query latency.
Measure and maintain index choices
Start with the exact SQL and representative parameter values. Run EXPLAIN, understand estimated rows and chosen keys, and collect latency and rows-examined data. A table scan can be optimal for a small table or a query returning a large fraction of rows. Refreshing optimizer statistics may help when estimates no longer reflect the data, but do not use it as a substitute for understanding query shape. Test index changes against realistic data volume and concurrent write activity.
Use indexes to serve known access patterns: foreign-key joins, selective filters, and common ordering. Composite indexes can cover multiple needs, but they should remain understandable and justified. Keep the primary key compact enough for the workload, and review index sizes as the dataset grows. See query optimization and InnoDB storage behavior.
Indexes are a trade-off, not a free performance switch. Design them from measured queries, validate with execution plans, and revisit them as workloads evolve.