Boost MySQL Performance: Proven SQL, Index, and Hardware Optimization Techniques

SQL Optimization

The article begins by outlining the major categories of MySQL optimization—SQL tuning, schema design, and hardware improvements—then dives into concrete techniques for each.

Pagination Optimization

Two approaches are described:

Delayed join : first fetch primary keys with a WHERE clause, then join back to the original table using those keys.

Bookmark method : locate the primary‑key value of the first row in the LIMIT window and filter by that value.

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10) b where a.id = b.id;

select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289,1) limit 10;

Index Optimization

Key points include using appropriate indexes, creating covering indexes to avoid table look‑ups, avoiding OR conditions before MySQL 5.0, and preferring index merges when possible. The article shows how to create a composite index for a query on city and name:

alter table test add index idx_city_name (city, name);

It also warns against using != or <> because they force full table scans, suggesting rewriting such predicates as range conditions combined with OR.

Prefix Indexes

When a column has a common suffix (e.g., email domains), a prefix index can reduce storage and improve lookup speed, though it cannot be used for ORDER BY or as a covering index.

alter table test add index index2(email(6));

Selective Column Retrieval

Avoid SELECT *; specify only needed columns to reduce I/O and network bandwidth.

Subquery and Join Optimization

Prefer JOIN over subqueries because subqueries create temporary tables. Use small tables to drive large tables, and ensure join columns are indexed. Adjust join_buffer_size carefully to avoid excessive memory consumption.

Sorting Optimization

Design indexes that match the ORDER BY clause so MySQL can produce ordered results directly from the index scan.

-- create index (date, staff_id, customer_id)
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id, customer_id;

UNION Optimization

Push WHERE and LIMIT clauses into each sub‑query, and use UNION ALL unless duplicate elimination is required.

Slow Query Log

Enable the slow‑query log with slow_query_log=1 and set an appropriate long_query_time. The article notes the performance impact of logging and advises cautious use in production.

Schema Design Optimization

Recommendations include avoiding NULL columns, using the smallest suitable data types, preferring INT over VARCHAR when possible, limiting TEXT columns, and applying sharding (splitting tables and databases) to reduce row counts per table.

Normalization levels are explained: 1NF (atomic fields), 2NF (eliminate partial dependencies), 3NF (remove transitive dependencies), with a note on the trade‑off of increased joins.

Hardware Optimization

Three hardware aspects affect MySQL performance:

Disk : Use high‑performance SSDs and consider multiple disks for parallel I/O.

Network : Ensure low latency and sufficient bandwidth; multiple NICs can help under heavy load.

Memory : Larger RAM allows more data caching, dramatically speeding up queries.

The article concludes with a brief invitation to follow the author’s WeChat channel for further discussion.