Finding and fixing the queries that cost time.
FINDING THEM
The slow query log records queries exceeding a threshold. Enable it, run the site normally, then read it.
Application profiling tools show queries per page request with durations, which is more immediately actionable.
READING EXPLAIN
Running EXPLAIN before a query shows the execution plan: which indexes are used, how many rows are examined, and whether a temporary table or filesort is needed.
Large row counts and missing index usage are what to fix.
COMMON PROBLEMS
No index on a filtered column A query inside a loop, running hundreds of times per page instead of once SELECT * retrieving columns you do not use A JOIN across large tables without indexes on the join columns ORDER BY on an unindexed column
FIXING
Add the index Restructure the loop into a single query Select only the columns you need Cache the result if it does not change often
MEASURING
Time the query before and after. Assumptions about what helped are frequently wrong.
