The MySQL optimizer already picked an access method for every table in your SELECT. EXPLAIN is how you inspect that plan: the type column names the access method, and Extra names extra work such as a filesort or a covering index. A desktop GUI keeps the statement in the editor so you can run the plan as a grid, then open indexes without copying output out of the mysql CLI.
Run the plan without executing the SELECT
Traditional EXPLAIN does not run the query. That matters on a live server when the SELECT would scan millions of rows. Connect first: 127.0.0.1 port 3306 for a local MySQL 8.4 or MariaDB 11 install, or the mapped Docker port. Open a SQL tab on the database that owns the slow statement.
Use this sequence:
- Paste a SELECT only. Keep UPDATE and DELETE out of this tab until you know the plan.
- If the app has an Explain control, click that instead of Run. TabulaSQL puts Explain on the query toolbar and prefixes
EXPLAIN FORMAT=JSONon the first statement so a plan tree can render. - For the classic type, key, rows, and Extra columns, run
EXPLAIN SELECT ...yourself with no FORMAT clause, then read the result grid left to right. - In TabulaSQL, toggle Raw grid after Explain if you want the JSON blob. Workbench uses Query, Explain Current Statement. DBeaver has Explain plan on the editor. HeidiSQL expects you to type EXPLAIN in front of the SELECT.
- Ignore id and select_type until type, key, rows, and Extra make sense.
Ctrl/Cmd+Enter still means Run. Do not mix that shortcut with Explain. The plan is cheap. The SELECT might not be.
type is a ranking, not a yes or no
The type column (access_type in JSON) is ordered from cheap to expensive for a single table. Read it before you invent an index.
| type | Meaning | Typical SQL |
|---|---|---|
| const | One row by primary or unique key | WHERE id = 42 |
| eq_ref | One row per row from the previous join table | JOIN customers c ON c.id = o.customer_id |
| ref | Non-unique index lookup | WHERE customer_id = 7 |
| range | Index scan over a range of keys | WHERE created_at >= '2026-01-01' |
| index | Full scan of the index tree | SELECT id FROM orders ORDER BY id |
| ALL | Full table scan | No usable index on the WHERE or JOIN column |
const and eq_ref are what you want on the inner table of a JOIN. ref is normal for a non-unique foreign key. range is fine for BETWEEN, IN, and inequalities when an index exists. ALL on a 20-row lookup table is noise. ALL on a large orders table is the plan you came to fix.
key names the index MySQL chose. NULL plus type ALL means it chose none. rows is an estimate of rows examined per scan, not the final result size. filtered (MySQL 5.7+) is the estimated percentage that pass WHERE after that scan. LIMIT 50 does not turn ALL into ref.
Extra hides filesort, temp tables, and covering indexes
Extra is a semicolon-separated list of flags, not a prose comment.
| Extra flag | JSON property | What it means |
|---|---|---|
| Using index | using_index | Covering index: MySQL reads the index tree only |
| Using filesort | using_filesort | A sort that the chosen index order could not satisfy |
| Using temporary | using_temporary_table | Internal temp table, often for GROUP BY or DISTINCT |
| Using where | often omitted in JSON | Rows filtered after they are read. Normal, not a warning |
| Using index condition | using_index_condition | Extra WHERE tests run against the index in the engine |
| Using join buffer (hash join) | hash join in the JSON | Hash join. Check this before you treat inner ALL as nested-loop |
Using filesort plus Using temporary on a large rows estimate is the slow GROUP BY ... ORDER BY pattern. Using index is good news. SELECT * often blocks a covering index because columns sit outside the key. For InnoDB, type index with key PRIMARY can be a clustered scan even when Using index is absent.
JSON trees still encode type and Extra
MySQL 8 accepts EXPLAIN FORMAT=JSON and EXPLAIN FORMAT=TREE. TabulaSQL's toolbar Explain uses FORMAT=JSON so the results panel can draw a tree from query_block. Each node can show access_type, key, rows_examined_per_scan, using_index, using_temporary_table, and using_filesort. Those are the same facts as type, key, rows, and Extra, with different names.
PostgreSQL uses EXPLAIN (FORMAT JSON) instead, and TabulaSQL can draw that tree too. The type and Extra columns in this guide are MySQL and MariaDB only. See the features page for what each driver covers.
If a tutorial pastes a grid and your GUI shows a tree, click Raw grid, or run:
EXPLAIN SELECT o.id, c.email
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open'
ORDER BY o.created_at DESC
LIMIT 50;
On an unindexed status column you should see type ALL on orders, key NULL, and Extra containing Using filesort. Add a composite index on (status, created_at), run EXPLAIN again, and look for type ref, a named key, and filesort gone because the index already orders created_at within each status.
Change the plan from the index manager
On MySQL and MariaDB, right-click the table in the object tree, open the index manager, and add columns in the order the WHERE and ORDER BY use them. Leading column should match equality filters (status). Following columns cover the range or sort (created_at). Unique is optional. Drop the experimental index if the next EXPLAIN still shows ALL.
ANALYZE TABLE orders updates optimizer statistics when key stays NULL even though the index exists. Safe Lock in TabulaSQL previews row writes. EXPLAIN ANALYZE actually executes the statement, so type it only on a copy of production.
Workbench, DBeaver, and HeidiSQL all show the same type and Extra values. They differ in whether you click a button or prefix EXPLAIN yourself. TabulaSQL is one free MIT-licensed client if you want Explain plus a visual tree in the same SQL tab: https://tabulasql.com/download
When the join looks wrong because you cannot see the foreign-key path, start with browsing MySQL foreign keys in a GUI.