Boost MySQL Query Performance with a Python‑Based Optimization Assistant

Introduction

The author, a seasoned MySQL database architect, describes the motivation for building a small Python utility that streamlines the repetitive steps involved in manual SQL tuning, such as examining execution plans, gathering statistics, and adjusting optimizer parameters.

Optimization Techniques and Workflow

Before using the tool, the article reviews common MySQL optimization methods, focusing on the EXPLAIN command and its variants ( EXPLAIN QUERY, EXPLAIN EXTENDED QUERY, EXPLAIN PARTITIONS QUERY, EXPLAIN FORMAT=JSON). It explains the meaning of each output column (e.g., id, select_type, type, possible_keys, key, key_len, ref, rows, Extra) and how to interpret them to identify bottlenecks.

SQL Rewrite

Using EXPLAIN EXTENDED together with SHOW WARNINGS, MySQL can rewrite queries (e.g., converting IN‑subqueries to joins). An example image illustrates this transformation.

Statistics Collection

The article lists two major types of statistics:

Table statistics obtained via SHOW TABLE STATUS (fields such as Name, Engine, Rows, Data_length, etc.).

Index statistics obtained via SHOW INDEX (fields like Table, Non_unique, Key_name, Seq_in_index, Column_name, Cardinality, Index_type, etc.).

System Parameters

Key MySQL variables that affect query performance are highlighted, including sort_buffer_size, join_buffer_size, tmp_table_size, and read_buffer_size, with advice on when to increase them.

Optimizer Switches

Parameters that control optimizer behavior are described: optimizer_search_depth, optimizer_prune_level, and the composite optimizer_switch. An example shows how disabling the ICP (Index Condition Pushdown) feature changes the Extra column.

Query Profiler

The built‑in MySQL Query Profiler is introduced, showing how to enable it ( SELECT @@profiling;, SET profiling=1;), execute a statement, and retrieve summary and detailed profiles with SHOW PROFILES and SHOW PROFILE FOR QUERY n.

Tool Overview

The Python utility, named “MySQL Statement Optimization Assistant”, automates the above steps. It requires the MySQLdb and sqlparse modules and runs on Python 2.7 (compatible with 2.6; 3.x not tested).

1. Prerequisites

Module: MySQLdb Module: sqlparse Python version = 2.7.3

2. Invocation

Run the script with a configuration file and the target SQL:

python mysql_tuning.py -p tuning_sql.ini -s 'select xxx'

3. Configuration File

The INI file has two sections:

[database]
server_ip   = 127.0.0.1
db_user     = testuser
db_pwd      = testpwd
db_name     = test

[option]
sys_parm    = ON   # show system parameters
sql_plan    = ON   # show execution plan
obj_stat    = ON   # show table/index stats
ses_status  = ON   # show before/after session status
sql_profile = ON   # show profiling info

4. Output Sections

The tool prints several labeled blocks (titles are shown in a blue banner in the original UI):

Header : connection info and MySQL version.

Original SQL : the formatted user query.

System Parameters : a subset of performance‑related variables hard‑coded in the script.

Optimizer Switches : values of the optimizer control variables.

Execution Plan : output of EXPLAIN EXTENDED.

Rewritten SQL : the optimizer’s transformed statement.

Statistics : table and index stats for all objects referenced by the query.

Runtime Status : differences in SHOW STATUS before and after execution.

PROFILE Details : raw profiling rows.

PROFILE Summary : aggregated resource consumption, highlighting bottlenecks.

Each section is illustrated with screenshots in the original article (preserved here as

tags).

Conclusion

By consolidating EXPLAIN analysis, statistics, optimizer tuning, and profiling into a single automated run, the tool dramatically reduces the manual effort required for MySQL query optimization, allowing DBAs to focus on higher‑level performance decisions.