Master MySQL Performance: Using EXPLAIN, Indexes, and Slow Query Analysis

MySQL Performance Analysis and the usage of EXPLAIN are the main topics of this article, illustrated with practical examples.

1. Using EXPLAIN to view analysis results

Running explain select * from test1 where id=1; produces columns such as id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra.

The type column values indicate access methods: const means the row is found via index in a single step; primary indicates the primary key is used; all denotes a full table scan; ref is used for multiple matching rows, typically in joins.

2. Composite indexes in MySQL

Assuming a table with columns id, key1, key2, key3 and a composite index on (key1, key2, key3), the leftmost‑most principle allows the index to be used for queries like SELECT * FROM test WHERE key1=1 ORDER BY key3. EXPLAIN shows that only the where clause benefits from the index, while the order by still requires sorting.

3. Using slow‑query analysis

In my.ini set:

long_query_time=1

log-slow-queries=d:\mysql5\logs\mysqlslow.log

Queries taking longer than one second are recorded in the slow‑query log, which can be examined with tools like mysqlsla or mysqlreport to see the percentage of SELECT, UPDATE, INSERT, DELETE, REPLACE, etc.

4. Locking differences between MyISAM and InnoDB

MyISAM uses table‑level locks, so a SELECT may be blocked by concurrent UPDATEs. InnoDB uses row‑level locks, avoiding this issue.

5. MySQL transaction configuration

Key settings: innodb_flush_log_at_trx_commit=1: Write transaction log to disk immediately on commit. innodb_flush_log_at_trx_commit=0: Write log to the OS buffer, flushing to disk every second. innodb_flush_log_at_trx_commit=2: Write to OS buffer immediately but flush to disk every second.

EXPLAIN syntax EXPLAIN tbl_name or EXPLAIN [EXTENDED] SELECT .... The latter provides detailed index information, which is the focus here.

Explanation of EXPLAIN columns

id : Query sequence number.

select_type : Type of SELECT (simple, union, subquery, etc.).

table : Table referenced by the row.

type : Access type, ordered from best to worst: system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL. Aim for at least range, preferably ref.

possible_keys : Indexes that could be used.

key : Index actually chosen (NULL if none).

key_len : Length of the chosen key; helps identify which part of a multi‑column index is used.

ref : Columns or constants compared with the key.

rows : Estimated number of rows examined (approximate for InnoDB).

Extra : Additional info, e.g., Using index (covers index only), Using where (WHERE clause applied), Using filesort or Using temporary (potential performance issues).

Understanding these fields helps diagnose and optimize MySQL queries.

That concludes the overview of MySQL performance analysis and EXPLAIN usage.

Author: Anonymous