Comprehensive Guide to MySQL Index Optimization and EXPLAIN Plan Analysis

Many developers spend minutes analyzing data but hours waiting for query results, so understanding SQL execution efficiency is essential; this guide covers three main parts: MySQL basics, SQL optimization methods, and detailed EXPLAIN plan analysis.

MySQL consists of a client layer (e.g., CMD, WorkBench, Navicat) and a server layer split into the SQL layer and the storage engine layer; query results are first cached in the query cache before being returned to the client.

To inspect available storage engines and related variables you can run SHOW ENGINES; and SHOW VARIABLES LIKE "%storage_engine%"; , and you can specify the engine for a table with statements like CREATE TABLE tb(... ) ENGINE=MyISAM; .

Indexes are the key to fast data retrieval. Simple examples include creating a single‑column index with CREATE INDEX dept_index ON tb(dept); , a unique index with CREATE UNIQUE INDEX name_index ON tb(name); , and a composite index with CREATE INDEX dept_name_index ON tb(dept, name); . You can also add indexes using ALTER TABLE tb ADD INDEX dept_index(dept); or ALTER TABLE tb ADD UNIQUE INDEX name_index(name); .

SQL optimization is required because poorly written queries (e.g., complex joins or sub‑queries) can cause long execution times. Manual tuning involves rewriting the query, while the MySQL optimizer may automatically rewrite it; however, the optimizer can sometimes override manual improvements, so understanding both approaches is important.

The EXPLAIN command reveals how MySQL will execute a query. Important columns include id (execution order), select_type (query type such as SIMPLE, PRIMARY, SUBQUERY, DERIVED), type (access type like system, const, eq_ref, ref, range, index, ALL), possible_keys (indexes that could be used), key (actual index used), key_len (length of the used index), rows (estimated rows examined), and extra (additional info such as Using filesort, Using temporary, Using index, Using where, Impossible where).

Index types affect performance: system and const are ideal but rare; eq_ref is used for unique indexes; ref works for non‑unique indexes; range handles range scans; index scans the whole index; ALL performs a full table scan.

Common pitfalls that cause index loss include using functions or calculations on indexed columns, range conditions ( > , < , IN ) that appear before other columns in a composite index, LIKE '%pattern%' , type conversions, OR conditions, and inequality operators ( != , <> ). To keep indexes effective, place the most selective column first, avoid cross‑column usage in composite indexes, and keep predicates simple.

Practical optimization examples: for a single table, create a composite index matching the WHERE clause order (e.g., CREATE INDEX typeid_authorid_bid ON book(typeid, authorid, bid); ); for multi‑table joins, let the smaller table drive the larger one and add indexes on the join columns; for ORDER BY, ensure the ordering columns are part of the index to avoid Using filesort . Index coverage (using only indexed columns) eliminates the need to read the base table, shown by Using index in EXPLAIN.

Key take‑aways: always design indexes with the leftmost prefix rule, add and drop indexes iteratively, place IN or range conditions at the end of the index, prefer EXISTS over IN when the sub‑query is large, and use index‑covered queries whenever possible to achieve the best performance.