Master MySQL Performance: Essential SQL Optimization Techniques & Index Strategies

Introduction

The author presents a consolidated guide of MySQL tuning experiences gathered from daily development, aiming to help developers improve database performance as data volumes grow.

SQL Optimization Steps

Use show status (or mysqladmin extended-status) to view server variables. Important variable groups include those prefixed with Com_ (command counts) and Innodb_ (row operations). Variables such as Com_select, Com_insert, Com_update, Com_delete, and Innodb_rows_read help determine whether the workload is read‑heavy or write‑heavy. Additional metrics like Connections, Uptime, Slow_queries, and Threads_connected give a broader view of server health.

Locating Slow Queries

Enable the slow‑query log (e.g.,

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

) and restart MySQL. Verify activation with show variables like "%slow%". When the log is on, examine slow_query_log entries. For real‑time monitoring, use show processlist or mysqladmin processlist to see active threads, their state, and the SQL being executed.

Analyzing Execution Plans with EXPLAIN

Run explain select * from test1; to view columns such as select_type (SIMPLE, UNION, SUBQUERY, etc.), type (system, const, eq_ref, ref, index_merge, range, index, all), possible_keys, key, key_len, rows, filtered, and extra. The type hierarchy indicates efficiency, with system being fastest and all slowest.

Indexes

Indexes accelerate data lookup. Types include FULLTEXT, HASH, B‑Tree (most common), and R‑Tree. Logical categories are normal, unique, primary, composite, and full‑text indexes. Creation examples:

create index normal_index on cxuan003(id);
create unique index normal_index on cxuan003(id);
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) NOT NULL,
    PRIMARY KEY (`id`)
);
CREATE FULLTEXT INDEX index_content ON article(content);

Indexes can be inspected with explain. Prefix indexes allow queries on the leftmost column of a composite index. However, indexes are ignored when:

Using OR where only one side has an index.

Querying a non‑leading column of a composite index.

Applying functions or arithmetic to indexed columns.

Using a leading % in a LIKE pattern.

Implicit type conversion forces a full table scan.

Using != or <> operators.

Monitoring Index Usage

Handler metrics indicate index effectiveness: Handler_read_key counts index reads; a low value suggests limited benefit. Handler_read_rnd_next counts random row reads; a high value signals many full scans and a need for better indexing.

Table Maintenance Commands

Regularly run:

analyze table cxuan005;
check table cxuan005;
optimize table cxuan005;

ANALYZE

updates statistics for better execution plans. CHECK verifies table integrity. OPTIMIZE defragments storage (effective for MyISAM, not InnoDB).

Common SQL Optimizations

Bulk Import

For MyISAM, disable/enable keys around large imports:

ALTER TABLE tblname DISABLE KEYS;
...load data...
ALTER TABLE tblname ENABLE KEYS;

For InnoDB, sort data by primary key, disable unique checks ( SET UNIQUE_CHECKS=0), and turn off autocommit ( SET AUTOCOMMIT=0) during import.

INSERT Optimizations

Batch multiple rows in a single INSERT.

Use INSERT DELAYED for MyISAM when appropriate.

Increase bulk_insert_buffer_size for MyISAM.

Place index and data files on separate disks.

GROUP BY / ORDER BY

Append ORDER BY NULL to suppress unnecessary sorting and avoid filesort overhead.

ORDER BY Optimization

When the ORDER BY column matches the leftmost index column, MySQL can use the index ("Using index") instead of a filesort. Create matching indexes and query in the same column order.

Nested Query Replacement

Replace inefficient subqueries with joins. Example:

explain select c05.id from cxuan005 c05 left join cxuan003 c03 on c05.id = c03.id;

This typically changes the execution type from index_subquery to ref, improving performance.

COUNT Optimization

Use COUNT(*) for row count; avoid counting a specific column when you need total rows, as COUNT(col) skips NULL values.

LIMIT Pagination

Large offsets (e.g., LIMIT 1000,50) are costly. Reduce page size or use key‑set pagination to avoid scanning many rows.

IN Clause

When the list is large, prefer BETWEEN or range conditions; excessive values in IN(...) increase memory usage.

Single‑Row Retrieval

Apply LIMIT 1 to force the optimizer to use a const access type.

WHERE Clause Best Practices

Avoid functions or arithmetic on indexed columns.

Do not use leading % in LIKE patterns.

Prefer = over != or <>.

Ensure column types match parameter types to prevent implicit conversion.

Select only needed columns instead of SELECT *.

Other Tips

Prefer UNION ALL over UNION when duplicate rows are impossible.

When possible, replace IN with BETWEEN for range checks.