Before reaching for a cache, make sure the database is doing its job. Indexes are the single most effective performance tool in relational databases.
Start with EXPLAIN
\\\sql
EXPLAIN SELECT p.id, p.title
FROM posts p
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 20;
\\\
If the type column says ALL, MySQL scanned the whole table. That is your problem, right there.
Composite indexes follow a rule
An index on \(status, publish_at)\ serves queries that filter on status and optionally order by publish_at. The order of columns matters: equality columns first, range and ordering columns last.
Covering indexes
When an index contains every column the query needs, MySQL answers it from the index alone — no table lookup. That is why our posts table indexes status, language and view counts separately.
What not to do
- Do not index every column "just in case": each index slows writes.
- Do not ignore the
slow_query_log. It is the cheapest monitoring you will ever set up.
