Before you start
You need a slow SELECT and a MySQL or MariaDB session where you can run it. By the end you will know which line of the plan is the slow step, and you can paste the plan into dbtempo to get an index or rewrite proposal.
Get the plan
Put EXPLAIN in front of the query and run it. This example looks up one customer's latest orders:
EXPLAIN
SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;The sample values below are made up for this guide. Your table and numbers will differ.
+----+-------------+--------+------+---------------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | rows | Extra |
+----+-------------+--------+------+---------------+------+--------+-----------------------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | 498120 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+--------+-----------------------------+Read the columns that matter
Ignore most of the table at first. Four columns tell you where the time goes:
In the sample, type is ALL, key is NULL, and MySQL expects to examine about 498,000 rows to return 20. That is the slow step: the query reads the whole table, then sorts what matches. An index on (customer_id, created_at) would let MySQL jump to one customer's rows already in order.
Have dbtempo analyze it
- Open a new thread. Select New thread, then Paste SQL or EXPLAIN.
- Paste the query, then the plan. dbtempo reads the grid exactly as MySQL prints it, with or without the
+----+borders, and also readsEXPLAIN FORMAT=JSONoutput. No database connection or CLI is needed. - Review the proposal. Read the diagnosis, evidence and confidence before you act. For a plan like the sample, expect a proposed composite index or a rewrite. dbtempo does not change your database; you apply the change through your own migration process.
Verify the fix
After you add the index in a test environment, run the same EXPLAIN again. key should name the new index, type should no longer be ALL, and the filesort should be gone. Next, see Read a PostgreSQL EXPLAIN plan if you also run Postgres, or upload a slow-query log to find which queries to explain first.