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
- performance_schema statement digests: calls, total time and rows examined for each query shape, read on every scan.
- 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: the body is fetched with SHOW CREATE PROCEDURE and the statements inside are explained one at a time.
How the cause is proven
Deterministic rules read the plan before any AI model does. On MySQL and MariaDB they include Full Table Scan, Filesort or Temporary Table, High Rows Examined Ratio, Missing Composite Index, SELECT * on Wide or BLOB Table, and Unbounded Pagination. The analysis then explains the cause in plain words, lists the evidence it relied on, and gives its 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 Full Table Scan rule fires, and 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.