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

SQL Optimization

Improving MySQL performance starts with optimizing SQL statements and indexes. Common techniques include refining pagination queries, reducing unnecessary scans, and restructuring queries for better index utilization.

Pagination Optimization

Typical inefficient query:

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

Effective alternatives:

Delayed join – first fetch primary keys, then join on the primary key.

Bookmark method – locate the primary key of the first LIMIT offset and filter by it.

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

Proper index usage is crucial. Adding indexes to frequently filtered columns prevents full‑table scans. Covering indexes store all required columns in the index leaf, eliminating the need for a table lookup.

Creating a covering index:

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

Before MySQL 5.0, avoid OR queries; use UNION or subqueries instead. After 5.0, MySQL can merge indexes via index_merge. Replace != or <> with equivalent OR conditions to keep indexes usable.

Use prefix indexes for long string columns when only a fixed prefix is selective, e.g.: alter table test add index index2 (email(6)); Never use SELECT *; list only needed columns to reduce I/O and network load.

Avoid functions or arithmetic on indexed columns, as they prevent index usage. Watch for implicit type conversion, such as comparing a VARCHAR column with an integer literal, which forces a full scan.

Adding redundant fields can reduce costly joins, and composite indexes must follow the left‑most prefix rule (e.g., idx(name,age,sex) works for queries starting with name or name,age but not for sex,name).

Join Optimization

MySQL uses a nested‑loop join algorithm. Reduce loop iterations by indexing join columns and driving the join with the smaller result set.

When the optimizer cannot determine the smaller table, an INNER JOIN lets MySQL automatically choose the small‑table‑driven strategy.

Avoid joining too many tables in a single statement because each additional table consumes join buffer memory ( join_buffer_size) and can lead to out‑of‑memory errors.

Sorting Optimization

MySQL can produce ordered results either by an explicit ORDER BY sort or by scanning an index whose column order matches the ORDER BY clause. Ensure the index columns appear in the same order and direction as the sort requirements.

-- 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

UNION builds a temporary table, which discards index benefits. Push down 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 to capture statements whose execution time exceeds long_query_time. Turn it on with slow_query_log=1, but be aware that logging adds overhead and should be used cautiously in production.

Design Optimization

Avoid NULL columns; prefer NOT NULL with default values. Choose the smallest appropriate data type, use INT instead of VARCHAR when possible, and limit the use of TEXT or BLOB columns.

Consider table partitioning and sharding (splitting tables or databases) to keep individual tables small and improve cache locality.

Data‑type specifics:

Integer width (e.g., INT(11)) does not affect storage; choose based on value range.

Use CHAR for fixed‑length strings, VARCHAR for variable length, balancing space and update fragmentation. DATETIME stores a wider range; TIMESTAMP is 4 bytes and time‑zone aware, often more space‑efficient.

Reserve BLOB / TEXT for truly large data; otherwise store in separate tables.

Normalization

Well‑normalized schemas reduce data duplication and improve cache efficiency, though they may increase join complexity. Follow the first, second, and third normal forms to balance read performance and data integrity.

Hardware Optimization

Invest in fast SSDs, multiple disks for parallel I/O, high‑throughput low‑latency network interfaces, and ample RAM. Larger memory allows more data and index pages to stay cached, dramatically speeding up query processing.