Master MySQL Performance: Practical SQL, Index, Join, and Hardware Tuning

Pagination Optimization

Large‑offset pagination can be inefficient; the article recommends three techniques: delayed join, bookmark method, and using a sub‑query to fetch primary keys first, then join back to the original table.

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

Delayed join: first select primary keys, then join on the primary key instead of scanning the secondary index.

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;

Bookmark method: find the primary‑key value of the first row of the limit, then filter by that key.

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 dramatically reduces full‑table scans. Key points include creating covering indexes, avoiding OR before MySQL 5.0, not using != or <>, employing prefix indexes when appropriate, and selecting only required columns.

Build covering indexes

When the index’s leaf nodes contain all queried columns, MySQL can return results directly from the index without a table lookup.

select name from test where city='上海';

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

Avoid OR queries before MySQL 5.0

Use UNION or sub‑queries instead, as early versions may invalidate indexes.

Avoid != or <> in WHERE

Rewrite column<>'aaa' as column>'aaa' OR column<'aaa' to keep index usage.

Use prefix indexes wisely

Define a shorter prefix for long string columns to save space, e.g., indexing the first six characters of an email.

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

Select specific fields instead of SELECT *

Fetching only needed columns reduces I/O and network bandwidth.

Optimize sub‑queries

Prefer JOIN over sub‑queries because sub‑queries create temporary tables that add overhead.

Small‑table drives large‑table

When possible, query the smaller table first to limit the number of rows processed in the larger table.

select name from A where id in (select id from B);

Avoid arithmetic on indexed columns

Expressions like id + 1 = 50 prevent index usage. select * from test where id + 1 = 50; Implicit type conversion can also break indexes; ensure literal types match column types (e.g., compare varchar with string, not integer).

Join Optimization

MySQL uses a nested‑loop join algorithm. Performance improves by indexing join columns, driving the join with the smaller result set, and limiting the number of nested loops.

Set join_buffer_size appropriately; excessive joins on many tables can exhaust memory.

Sorting Optimization

Use an index that matches the ORDER BY clause to avoid extra sorting steps. The index column order must exactly follow the order clause.

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

MySQL builds a temporary table for UNION, which can bypass indexes. Push down WHERE and LIMIT into each sub‑query and prefer UNION ALL unless duplicate elimination is required.

Slow Query Log

Enable the slow‑query log by setting slow_query_log=1 and configuring long_query_time. Use the log together with EXPLAIN to pinpoint performance bottlenecks, but be aware it adds overhead in production.

Schema Design Optimization

Avoid NULL columns, use the smallest appropriate data types, prefer INT over VARCHAR when possible, limit the use of TEXT and BLOB, and consider vertical or horizontal partitioning (splitting tables or databases) to keep frequently accessed data in smaller, faster tables.

Common Type Choices

Integer width (e.g., int(11)) does not affect storage; choose based on range needs. CHAR is fixed‑length, VARCHAR is variable‑length with extra length bytes. DATETIME is timezone‑agnostic and larger; TIMESTAMP is 4 bytes and timezone‑aware, generally preferred for space efficiency.

Normalization

Higher normal forms reduce data redundancy and improve cache efficiency, though they may increase join complexity. Follow 1NF, 2NF, and 3NF guidelines when designing tables.

Hardware Optimization

Use fast SSDs, consider multiple disks for parallel I/O, ensure low‑latency network with sufficient bandwidth (multiple NICs if needed), and provision ample memory so that buffers and caches can reside in RAM, which dramatically speeds up MySQL operations.