When a site feels “slow,” the database is usually the bottleneck. PHP is waiting on I/O. If MySQL can’t locate rows efficiently, your app will never feel fast.
1. Index what you filter on
Columns used in WHERE, JOIN, and common ORDER BY clauses are your primary candidates. Indexes are not magic; they are data structures that speed lookups at the cost of write overhead.
2. Composite indexes beat multiple single indexes
If you regularly query by (user_id, created_at), a composite index in that order is often better than two separate indexes.
3. Use EXPLAIN, not guesses
Run EXPLAIN on your slow queries. Look for full table scans, poor selectivity, and missing index usage.
4. Avoid functions on indexed columns
WHERE DATE(created_at) = ... prevents index use. Prefer range queries:
5. Covering indexes
If the index contains all the columns needed for a query, MySQL can answer without reading the table rows. That’s a covering index and it’s one of the biggest wins you can get.
Indexing is a discipline: measure, add the smallest index that solves the query, then re-measure.