Boost MySQL Performance: Proven SQL Optimization Techniques

Introduction

As applications scale and data volume increases, MySQL‑based systems often suffer from latency, slow queries, and overall performance degradation. This guide collects a series of concrete SQL optimization techniques, demonstrated with test tables (student, class, account) and real‑world examples, to help developers and architects tune their databases.

1. Query Optimization

High‑frequency read‑heavy workloads benefit from careful query design. The article first creates a test table with 100,000 rows using a stored procedure:

create procedure addMyData()
begin
  declare num int;
  set num = 1;
  while num <= 100000 do
    insert into XXX_table values(
      replace(uuid(), '-', ''),
      concat('测试', num),
      concat('cs', num),
      '123456'
    );
    set num = num + 1;
  end while;
end;

After populating the data, the procedure is invoked: call addMyData(); Three tables are prepared for testing: student (500k rows), class (10k rows), and account (100k rows).

2. Pagination Query Optimization

Large offsets in LIMIT cause MySQL to sort and discard many rows, leading to high cost. Two approaches are shown:

Use a subquery to fetch only the primary keys for the desired page, then join back to the main table:

SELECT * FROM student t1,
       (SELECT id FROM student ORDER BY id LIMIT 400000,10) t2
WHERE t1.id = t2.id;

When the primary key is auto‑increment, replace the offset with a range condition:

SELECT * FROM student WHERE id > 400000 LIMIT 10;

Both queries demonstrate measurable response‑time improvements.

3. Join Query Optimization

Joins are ubiquitous. The key is to ensure join columns are indexed. Example left join:

SELECT t.* FROM student t LEFT JOIN class cs ON t.classId = cs.id;

Explain shows a full‑table scan on student and an index scan on class. Recommendations:

Place frequently used columns in primary‑key or covering indexes.

Prefer the smaller table as the driving (left) side of the join.

Index creation for the join condition:

CREATE INDEX idx_name ON tenant(tenant_name);
CREATE INDEX idx_account ON `user`(account);

After adding indexes, EXPLAIN shows the type column changing to ref, indicating index usage.

4. Subquery Optimization

Subqueries can materialize temporary tables, consuming CPU and I/O. An example with a large IN list:

SELECT st.* FROM student st WHERE st.classId IN (
  SELECT id FROM class WHERE id > 100
);

Explain reveals a full‑table scan despite the inner query using an index. Rewriting as a join eliminates the temporary table and improves performance:

SELECT st.id FROM student st JOIN class cl ON st.classId = cl.id WHERE cl.id > 100;

A real‑world case replaces a subquery that caused a 5‑second response with an equivalent join, reducing latency dramatically.

5. ORDER BY Optimization

MySQL offers two sorting strategies:

Using filesort : reads rows then sorts in a buffer; slower.

Using index : scans an ordered index and returns rows already sorted; faster.

When sorting by a non‑indexed column (e.g., age), MySQL falls back to filesort. Adding an index on age forces the index‑based path: CREATE INDEX idx_age ON student(age); For multi‑column sorting, a composite index must follow the left‑most prefix rule. Example composite index on stuno and age:

CREATE INDEX idx_stuno_age ON `student`(stuno, age);

If the sort order or column order violates the left‑most prefix, MySQL reverts to filesort.

6. GROUP BY Optimization

Group‑by benefits from the same principles as order‑by. Key points:

Indexes can be used even without explicit WHERE filters.

Follow the left‑most prefix rule for composite indexes.

Increase sort_buffer_size when necessary.

Prefer filtering in WHERE over HAVING.

Avoid unnecessary GROUP BY on large result sets; keep filtered rows under ~1,000 for best performance.

Creating an index on the grouping column dramatically speeds up the operation, as shown by the before/after EXPLAIN screenshots.

7. COUNT() Optimization

The aggregate COUNT() function scans rows one by one. Different forms have varying overhead: COUNT(column) – slowest. COUNT(primary_key) – faster. COUNT(1) and COUNT(*) – fastest and equivalent.

Therefore, prefer COUNT(*) for counting rows.

Conclusion

Effective MySQL performance tuning relies on proper indexing, avoiding costly subqueries, using range‑based pagination, and aligning ORDER BY / GROUP BY clauses with index structures. The article provides concrete SQL statements, EXPLAIN analyses, and visual performance evidence to guide developers in applying these optimizations.