Interpreting EXPLAIN Output Columns

Each SELECT inside a query receives a unique identifier shown in the id column. The select_type describes the role of that particular SELECT:

• SIMPLE: A plain query without subqueries or unions.
• PRIMARY: The outermost SELECT when subqueries exist.
• DERIVED: A subquery appearing in the FROM clause; MySQL materializes its result into a temporary table.

The table column names the accessed table or alias. type indicates the join method, ranging from system/const (best) to ALL (full table scan). possible_keys lists indexes the optimizer considered, while key shows the one actually chosen. key_len reveals how many bytes of the index are used, and ref indicates which columns or constants are compared against the index. rows is the optimizer’s estimated number of examined rows.

The Extra field provides critical operational details:

• Using filesort: Sorting happens without an index; often a target for optimization.
• Using temporary: A temporary table is used for operations like GROUP BY or ORDER BY when no suitable index exists. This frequent appears alongside Using filesort because grouping implies sorting.
• Using index: The query retrieves all needed columns directly from the index tree, avoiding table row lookups—an efficient covering-index scan.
• Using where: A WHERE clause filters rows after storage engine retrieval.

Covering Index Concept

A covering index includes every column referenced by a query, so the engine can supply results entirely from the index without touching the clustered data.

Conditions That Prevent Index Usage

1. Violating the leftmost prefix rule: A composite index requires leading columns to appear in query conditions. Skipping a column breaks the matching chain.
2. Applying functions or arithmetic on indexed columns: Expressions like WHERE YEAR(date_col) = 2024 forbid index access.
3. Range conditions on a composite key column nullify index usage for columns to its right.

   CREATE INDEX idx_multi ON users (user_id, age, name);
   -- Full index usage
   SELECT * FROM users WHERE user_id = 1 AND age = 12 AND name = 'zhangsan';
   -- Index stops at age range; name part ignored
   SELECT * FROM users WHERE user_id = 1 AND age > 12 AND name = 'zhangsan';
   

4. Inequality operators (!=, <>): Usually trigger a full scan.

   SELECT * FROM users WHERE user_id != 1;
   

5. IS NOT NULL disables index lookups, while IS NULL can still use an index.

   EXPLAIN SELECT * FROM users WHERE name IS NOT NULL;
   

6. Leading wildcadr in LIKE patterns forces a full table scan.

   SELECT * FROM users WHERE name LIKE '%ming';
   

7. Omitting quotes around a string literal in a comparison causes type mismatches and index neglect.

   SELECT * FROM users WHERE name = zhangsan;
   

8. Combining conditions with OR involving non-indexed columns disables index usage.

   SELECT * FROM users WHERE user_id = 1 OR age = 12;
   

Sorting and Join Tuning Strategies

• Avoid Using filesort by ensuring ORDER BY columns map to an index’s leftmost columns, optionally combined with WHERE conditions that maintain the prefix.
• A range condition in the WHERE clause breaks index-based sorting when followed by an ORDER BY.
• For join queries, ensure join column are indexed and consider the join order based on row estimates.

Understanding MySQL Pages

The term page in MySQL refers to two distinct concepts:

• Storage engine data page: The fixed-size block (e.g., 16 KB in InnoDB) used to store rows, indexes, and undo logs on disk and in the buffer pool.
• Result set page in execution plans: An abstract unit representing an intermediate or final batch of rows generated during query execution steps.