Finding the query at fault.
WHAT TO LOOK AT FIRST
The process list, for queries currently running.
WHAT THE TIME COLUMN SHOWS
How long each has been running.
WHAT A LONG-RUNNING QUERY INDICATES
Usually a missing index, or a table scan.
WHAT THE STATE COLUMN REVEALS
What it is waiting on: a lock, a sort, copying to a temporary table.
WHAT THE SLOW QUERY LOG PROVIDES
A record of queries exceeding a threshold.
WHAT TO SET THE THRESHOLD TO
Low enough to catch problems, high enough to be readable.
WHAT EXPLAIN DOES
Shows how the database intends to execute a query.
WHAT TO LOOK FOR IN IT
Whether an index is used How many rows are examined
WHAT EXAMINING FAR MORE ROWS THAN RETURNED MEANS
A missing or unusable index.
WHAT MAKES AN INDEX UNUSABLE
A function applied to the column A leading wildcard in a pattern match A type mismatch
WHAT TO CHECK ON A WEB SERVER
Which application is issuing the query.
WHY
The fix is usually in the application, not the database.
WHAT TO BE CAREFUL ABOUT WHEN ADDING INDEXES
They cost write speed and space, and on a large table the operation locks.
WHAT TO DO
Test on a copy, and time it.
