Finding the query that is slow

A slow page is usually one slow query, not a slow server. MySQL will tell you which one if you ask, and asking costs one setting.

Turn it on

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

One second is a good threshold to start: it catches what matters without filling the disk. Make it permanent in my.cnf once you know it is useful.

Read it

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

That sorts by total time and prints the ten worst. A query that takes 0.2s but runs three hundred times a page is worse than one that takes 2s once, and this ordering shows that.

Ask why it is slow

EXPLAIN SELECT ... ;

• type: ALL - a full table scan. Every row read to answer one question.
• rows: a large number - how many it expects to read. Compare it to how many you expect back.
• Using filesort - sorting in memory or on disk because no index gives the order.

The index that usually fixes it

An index on the column in the WHERE clause. If the query filters on two columns, one index over both, in the order the query uses them.

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);