MySQL EXPLAIN: Index Analysis and Query Optimization Deep Dive

Index Analysis and Optimization

MySQL provides an EXPLAIN command that can analyze SELECT statements.

EXPLAIN SELECT * from wzk_user WHERE id < 1;

select_type

Represents the query type:

• SIMPLE: Simplest query type
• PRIMARY: Outermost main query
• UNION: Second or subsequent query of UNION operation
• SUBQUERY: Independent subquery statement

type

Represents the method used by storage engine to query data, from low to high efficiency:

• ALL: Full table scan
• index: Index-based full table scan
• range: Use index for range query
• ref: Use non-unique index for single value query
• eq_ref: Use primary key or unique index in multi-table JOIN
• const: Use primary key or unique index for equi-query
• NULL: Can get result without accessing table

possible_keys

Represents the list of indexes that MySQL optimizer may consider using.

key

Represents the index name actually selected by the query optimizer. When NULL, no index is used.

rows

MySQL query optimizer estimates how many rows SQL needs to scan to obtain results. In principle, the fewer rows, the higher the efficiency.

key_len

Represents the number of bytes of index used in the query.

Calculation rules:

• String type: latin1=1, gbk=2, utf8=3, utf8mb4=4
• Numeric type: TINYINT=1, SMALLINT=2, INT=4, BIGINT=8
• Time type: DATE=3, DATETIME=8

Extra Information Explained

• Using where: Need to query data through index table lookup
• Using index: Query can be completed only through index (covering index)
• Using filesort: Query results need additional sorting
• Using temporary: Query uses temporary table
• Impossible WHERE: WHERE condition is always false