Do not create an index for every column that appears in a MySQL WHERE clause. Start with the queries that matter, then add the smallest set of indexes that measurably improves them. Each extra index consumes storage and adds work to writes and query planning.
Last updated: September 30, 2026.
CREATE TABLE Category (
id BIGINT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
name VARCHAR(100) NOT NULL,
INDEX ix_category_tenant_status (tenant_id, status)
);
EXPLAIN SELECT id, name
FROM Category
WHERE tenant_id = 42 AND status = 'active';This composite index supports the two-column filter and a search on tenant_id alone. It does not provide an equally direct lookup on status alone, because MySQL uses the leftmost prefix of a composite index for index lookups. Add a separate status index only if important status-only queries benefit from it in testing.
Map indexes to real query patterns
List frequent or slow queries, their filter columns, joins, and sort order. For WHERE tenant_id = ? AND status = ?, one composite index can be more useful than two independent indexes. If another important query filters on created_at without tenant_id, the example index will not cover that access pattern. MySQL explains the leftmost-prefix rule and how it may combine separate indexes.
Do not assume that a predicate always needs an index. A small table or a condition matching most rows may be faster to scan. Avoid adding a wider index solely because a column appears in a filter; account for the extra space and maintenance. MySQL’s index guidance describes those costs. The WHERE guide and AND/OR examples help identify the actual predicates your application issues.
Verify before keeping an index
Run EXPLAIN for representative queries before and after the change. Check the chosen key, estimated rows, and access type; compare real latency under representative data and load. On a test system, EXPLAIN ANALYZE executes a SELECT and reports actual timings, so avoid using it blindly on an expensive production query. The MySQL EXPLAIN reference documents both forms.
Keep an index when the read benefit justifies its effect on inserts, updates, deletes, and storage. Compare table and index growth with the MySQL size query. Revisit the set when query patterns change, and remove redundant indexes after verifying that no important plan relies on them.
