The EXPLAIN command is the primary tool for inspecting how MySQL plans to execute a SELECT query. Its output reveals the table access order, the join type used for each table, which indexes (if any) the optimizer chose, and the estimated number of rows examined at each step. By comparing plans before and after a change, you can verify whether a new index is actually being used, whether a rewrite improved selectivity, or whether a configuration change altered join ordering. In MySQL 8, EXPLAIN ANALYZE goes further by actually executing the query and reporting real runtime statistics such as actual row counts and timing, which makes it far more useful than estimates alone for diagnosing real performance problems.
Several columns in EXPLAIN output deserve careful attention. The type column shows how MySQL accesses rows: values such as system, const, eq_ref, ref, range, index, and ALL indicate progressively less efficient access, with ALL representing a full table scan—the worst case for performance. The rows column reports the optimizer's estimate of how many rows must be examined at each step, so large numbers there often point to missing or poorly chosen indexes. The Extra column can also reveal inefficiencies: Using filesort indicates that MySQL cannot use an index for ordering, and Using temporary signals that an intermediate result was stored in a temporary table. GUI tools such as MySQL Workbench and EXPLAIN visualizers can render these plans more readably as join trees.
Beyond EXPLAIN, MySQL provides diagnostic data that complements plan inspection. High values of Handler_read_rnd_next, visible via SHOW STATUS LIKE 'Handler_read%', suggest excessive full scans, while steady growth in Created_tmp_disk_tables indicates that queries are spilling intermediate results to disk. The optimizer trace feature produces a detailed JSON record of the decisions the optimizer considered, which can be invaluable when a plan looks wrong but the cause is not obvious; you enable it by setting optimizer_trace within a session, running the query, then reading information_schema.OPTIMIZER_TRACE. While the trace output can be large for complex queries, it shows exactly which indexes, join orders, and strategies the optimizer rejected. Tools like the sys schema offer curated views over performance_schema and information_schema, making it easier to spot hotspots, top queries, and inefficient access patterns without manually sifting through raw metrics.