MySQL Index Optimization and Performance Analysis

This guide introduces MySQL indexing concepts, explains why queries can become slow, and shows how to use the EXPLAIN command to diagnose performance problems.

Case 1: Order import deduplication – The basic query

select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

results in a full table scan (type=ALL). Creating a unique index on transaction_id (

create unique index idx_order_transaID on itdragon_order_list (transaction_id);

) changes the execution plan to type=const, dramatically improving speed. Converting the query to

select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

yields a covering index (Extra=Using index), further reducing I/O.

Case 2: Order management sorting – Ordering by order_level, input_date initially triggers Using filesort. Adding a composite index

create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

does not help when selecting all columns, because MySQL still performs a full scan. Selecting only the indexed columns (

select order_level,input_date from itdragon_order_list order by order_level,input_date;

) changes the plan to type=index with Using index. For full‑row results, forcing the index (

select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;

) still uses the index but returns all fields. The article notes that sorting by a low‑cardinality column like order_level offers little benefit, and suggests filtering by a specific level before sorting.

The article then outlines index basics: definitions, advantages (faster retrieval, reduced sorting cost) and disadvantages (storage overhead, slower writes, maintenance complexity). It lists common index types (single‑column, unique, composite) and provides the basic SQL syntax for creating, altering, dropping, and showing indexes.

Guidelines are given for when to create indexes (primary/unique keys, frequent WHERE conditions, columns used for ORDER BY/GROUP BY, foreign keys) and when to avoid them (small tables, high write‑frequency tables, low‑cardinality columns, columns not used in queries).

Performance analysis sections describe MySQL bottlenecks (CPU, I/O, hardware), explain the meaning of each column in EXPLAIN output (id, select_type, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra) and common Extra values such as Using filesort, Using temporary, Using index, and Using index condition.

Finally, the article summarizes key take‑aways: indexes improve read speed but slow writes; many performance issues stem from missing or ineffective indexes; use EXPLAIN to diagnose; design tables to minimize joins; and consider archiving strategies.