The most important diagnostic tool for query tuning in MySQL is the EXPLAIN statement, which shows how the server intends to execute a SELECT query. The output reveals the order in which tables are accessed, the join type used for each table, which indexes (if any) are involved, the estimated number of rows examined at each step, and additional information about sorts, temporary tables, and other operations. Reading EXPLAIN fluently is the foundation of nearly every optimization decision.
The `type` column in EXPLAIN output describes the join type and how rows are accessed, ranging from the most efficient (system, const) through efficient indexed lookups (eq_ref, ref, range) down to `index` and finally `ALL`, which is a full table scan. Whenever you see `ALL` for a non-trivial table, that is usually a strong signal that an index is missing or unused. The `rows` column gives an estimate of how many rows MySQL expects to examine for that step, and large values combined with poor join types are a clear warning sign of inefficiency.
Beyond join types, the `Extra` column exposes critical details about the plan. A `Using filesort` entry indicates that MySQL cannot use an index to satisfy the ordering, often because the ORDER BY columns do not match an index or because of mixed ASC/DESC directions. A `Using temporary` entry signals that MySQL needs a temporary table, which is expensive when it spills to disk. In MySQL 8, `EXPLAIN ANALYZE` goes further by actually executing the query and reporting real timing and row counts instead of just estimates, making it a powerful tool for verifying that the optimizer is doing what you expect. For deeper diagnosis, optimizer trace records the alternatives the optimizer considered and rejected, which can be invaluable when plans are surprising.