Overview
Index design begins with the queries an application actually runs, not with a rule that every foreign key or filter deserves an index. An index can reduce reads, but it also consumes storage and makes inserts, updates, and deletes more expensive.
Start with the workload and the invariant
Start with the slow query log or application tracing. Capture representative SQL, parameter values, frequency, latency, and rows returned. Run EXPLAIN on the exact query shape. In MySQL, inspect the selected key, estimated rows, access type, and whether the plan uses a temporary table or filesort. Estimates are useful, but compare them with actual timings and production-like data volumes.
Practical example
Consider a table orders queried by tenant and status, newest first. A candidate index is (tenant_id, status, created_at). The leftmost-prefix rule means it can support predicates beginning with tenant_id, and with both tenant_id and status. It is not equivalent to an index on (status, tenant_id, created_at). Choose order from equality predicates, range predicates, sorting, and selectivity; test alternatives rather than assuming a universal order.
CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at);
EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
Trade-offs and failure modes
Do not add every selected column to make an index covering. A covering index can avoid table lookups, but a very wide index increases cache pressure and write amplification. Prefix indexes may help long strings, but they can reduce selectivity. For text search, ordinary B-tree indexes are not a substitute for a suitable full-text or external search design.
Verification and rollout
Test on a staging copy with realistic cardinality and distribution. Compare the same query before and after, including warm-cache and cold-cache behavior where practical. Check write throughput and index size. After deployment, monitor slow-query frequency and latency percentiles. Remove redundant indexes only after checking constraints, query plans, and application usage over a representative period.
Engineering checklist
An index is successful when it improves a meaningful workload without imposing unacceptable write or storage cost. Keep the query, plan, measurement, and reason for each non-obvious index in a short engineering note.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.