Master MySQL Optimization: From Status Variables to Index Tuning

SQL Optimization Steps

When a query needs optimization, start by examining server status variables using SHOW STATUS (or mysqladmin extended-status) to see execution counts and other metrics. Variables can be queried at session level (current connection) or global level (since server start). Important prefixes are Com_ (command counts) and Innodb_ (InnoDB metrics). Com_select, Com_insert, Com_update, Com_delete show how many times each DML statement runs. Innodb_rows_read, Innodb_rows_inserted, Innodb_rows_updated, Innodb_rows_deleted indicate rows processed by InnoDB.

Other useful metrics include Connections, Uptime, Slow_queries, and Threads_connected.

Locating Slow Queries

Enable the slow query log in my.cnf:

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

Check if it is active with SHOW VARIABLES LIKE "%slow%", then turn it on with SET GLOBAL slow_query_log='ON';. The log records queries exceeding the threshold after they finish, so for real‑time monitoring use SHOW PROCESSLIST or mysqladmin processlist. The output fields (Id, User, Host, Db, Command, Time, State, Info) help identify blocking or long‑running statements.

Analyzing Execution Plans with EXPLAIN

Run EXPLAIN SELECT * FROM test1; to see how MySQL will execute a query. Key columns include:

select_type : SIMPLE, PRIMARY, UNION, SUBQUERY, etc.

type : access method ranging from system (fastest) to all (full table scan). Intermediate values are const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, range, index.

possible_keys and key : indexes that could be used and the one actually chosen.

key_len , rows , filtered , extra : further details on index usage and row estimates.

Indexes Overview

Indexes accelerate data lookup. Types supported by MySQL include:

FULLTEXT (global text search, MyISAM only)

HASH (key‑value, fast point lookups, MEMORY engine default)

B‑Tree (most common, B+Tree variant)

R‑Tree (geometry data, limited engine support)

Common index categories:

Normal index: CREATE INDEX normal_index ON cxuan003(id); Unique index:

CREATE UNIQUE INDEX normal_index ON cxuan003(id);

Primary key: defined at table creation.

Composite index: multiple columns, follows left‑most prefix rule.

Full‑text index: for searching keywords in CHAR/VARCHAR/TEXT columns.

Using and Testing Indexes

Create a composite index and examine its usage:

CREATE INDEX id_hash_index ON cxuan005(id,hash);

Even if a query filters only on the first column ( id), the index is used due to the prefix property. Queries on the second column alone ( hash) do not use the index. LIKE patterns use the index only when the wildcard is not the leading character. Other cases where indexes are ignored include:

OR conditions where one side lacks an index.

Functions or expressions on indexed columns (e.g., CONCAT(id,'111')).

Leading % in LIKE.

Implicit type conversion causing full scans.

Operators like != or <> that never use indexes.

ORDER BY on a column not covered by an index.

Two status variables help evaluate index effectiveness: Handler_read_key: number of index reads; low values suggest limited benefit. Handler_read_rnd_next: number of full‑table scans; high values indicate missing or ineffective indexes.

Table Maintenance Commands

Regularly run these statements to keep tables healthy: ANALYZE TABLE cxuan005; – updates key distribution statistics for better optimizer decisions. CHECK TABLE cxuan005; – verifies table and index integrity. OPTIMIZE TABLE cxuan005; – defragments space for MyISAM/BDB; for InnoDB, recreate the table and analyze again.

These maintenance steps, combined with proper index design and query analysis, form a solid foundation for MySQL performance tuning.