MySQL slow query optimization

Start with the queries that cost the most time in total, not the single slowest run. Read their EXPLAIN plans for full scans, sorts that no index serves, and many more rows examined than returned. Then add the index or rewrite the query that removes the extra work. dbtempo does each step and keeps the evidence with the fix.

Where the slow queries come from

The statistics the database already keeps about each query (performance_schema).
The slow query log, read from the server over SSH or uploaded into a thread.
A query or an EXPLAIN plan pasted into a thread, with nothing connected.
Stored procedures are covered.

How the cause is proven

dbtempo reads the plan for the usual causes, then explains the cause with its evidence and confidence.

A worked example

A checkout query finds a customer by email and joins their orders. EXPLAIN shows type ALL on customers: every row is read to find one address. The recommendation is ALTER TABLE customers ADD INDEX idx_customers_email (email), stored with DROP INDEX idx_customers_email ON customers as its rollback.

Shipping the fix

Apply runs the change when the project allows index changes, either at once or after someone approves it in the Awaiting approval queue. It is recorded on the database’s audit trail with its rollback SQL. When the fix belongs in application code and the repository is connected, dbtempo opens a draft pull request instead.

Where the slow queries come from

The statistics the database already keeps about each query (performance_schema).
The slow query log, read from the server over SSH or uploaded into a thread.
A query or an EXPLAIN plan pasted into a thread, with nothing connected.
Stored procedures are covered.