SQL Statement Optimization Practices for MySQL

SQL statement optimization is a familiar yet complex topic; while databases continuously improve internal optimizers, developers should prepare queries before they reach the engine to let the database focus on what it does best.

In MySQL, a query passes through lexical, syntactic, semantic parsing, logical optimization, physical optimization, and execution. DBAs can influence logical optimization and some physical optimization, aiming to align queries with built‑in rules so fewer rows and columns are scanned at each stage.

Logical optimization involves applying numerous built‑in rules such as replacing sub‑queries with joins, pushing group‑by conditions, using inner joins instead of outer joins when possible, and pushing view logic down to base tables.

Physical optimization lets the optimizer choose the best execution path based on table statistics, column statistics, index count and quality, current load, and hardware resources.

General steps for analyzing a “slow” or “poorly optimized” SQL statement

1. Examine the involved tables and their storage engine

Ensure all tables use the InnoDB engine (MySQL 5.5+ optimizations target InnoDB).

Check for temporary tables and evaluate whether they can be replaced by disk tables.

Review column data types for any unreasonable definitions.

Inspect row counts to see if tables are excessively large and may need splitting.

Verify that table statistics are up‑to‑date.

Assess index design; possible outcomes include: No indexes at all, not even a primary key. Only primary or unique indexes, no secondary indexes. Primary/unique indexes plus well‑chosen secondary indexes. Primary/unique indexes plus secondary indexes with poor selectivity.

2. If some objects are views, consider the following

Identify the view algorithm (TEMPTABLE vs MERGE) and test which yields better performance.

Simplify complex view logic or move it to the application layer to avoid overburdening the database.

If the view is not essential, break it into base tables and let the outer query handle the joins, which often improves optimizer choices.

3. For multi‑table joins, verify join keys

If join keys are primary‑key/foreign‑key pairs, add extra filter conditions to reduce scanned rows.

If join keys are non‑unique columns, redesign the schema to use unique keys for joining.

Ensure join columns use the same character set/collation; convert if necessary.

4. Analyze the SQL statement itself

If the statement is already simple, no rewrite may be needed; performance issues could stem from excessive join count (e.g., a 20‑table join).

If the statement is complex, break it down—sub‑queries, nested joins, multiple aggregations—each has specific tuning techniques that will be covered later.

5. After rewriting, evaluate the execution plan (ideally under production‑like load)

If the plan uses optimal indexes and the statement is concise, the rewrite is complete.

If the plan does not use suitable indexes, consider adding appropriate indexes; if performance improves, the rewrite is complete.

If the plan uses good indexes but performance is still lacking, investigate further: Index selectivity may vary with different filter values (e.g., yesterday’s data vs. two days ago). Large table size may still cause many rows to be scanned; consider partitioning, sharding, or limiting the query scope at the business level.

Following these steps, most queries can be significantly optimized. Future articles will detail each optimization technique and demonstrate how to apply them in MySQL.