Boost MySQL Performance: Proven SQL Optimization Techniques

Introduction

When a system runs for a while and data volume grows, latency and stutter often appear, prompting developers or architects to perform system tuning. Among many tuning techniques, SQL optimization remains crucial. The following sections summarize practical MySQL optimization strategies with concrete examples.

1. Pagination Query Optimization

Read‑heavy, write‑light workloads make pagination queries very frequent. Large offset values (e.g., LIMIT 400000,10) cause MySQL to sort and discard millions of rows, leading to high cost.

Optimization ideas:

Create covering indexes and use a sub‑query to fetch only the primary keys, then join back to the original table.

For auto‑increment primary keys, replace large offsets with a range condition.

Example using a covering index:

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

Result shows reduced response time.

Alternative range query:

SELECT * FROM student WHERE id > 400000 LIMIT 10;

Result also shows improved performance.

2. Join Query Optimization

Joins are ubiquitous. The key is to ensure join columns are indexed. The optimizer’s choice also depends on the storage engine.

2.1 Left/Right Join

Example left join:

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

EXPLAIN reveals a full table scan on the left table (student) and an index scan on the right (class). Recommendations:

Include join columns in primary or covering indexes.

Prefer the smaller table as the driving (left) table.

2.2 Indexing Join Columns

Example with tenant and user tables:

EXPLAIN SELECT u.* FROM tenant t LEFT JOIN `user` u ON u.account = t.tenant_name WHERE t.removed IS NULL AND u.removed IS NULL;

Adding indexes improves the type from ALL to ref and reduces rows examined.

CREATE INDEX idx_name ON tenant(tenant_name);

CREATE INDEX idx_account ON `user`(account);

After re‑running EXPLAIN, the tenant table becomes the driving table, demonstrating the optimizer’s dynamic choice.

2.3 Inner Join Indexing

Removing existing indexes and then adding appropriate ones can change the optimizer’s plan. Example:

ALTER TABLE `user` DROP INDEX idx_account;

ALTER TABLE `tenant` DROP INDEX idx_name;

After adding back the indexes, EXPLAIN shows different driving tables, confirming that inner‑join plans are optimizer‑driven.

3. Subquery Optimization

Subqueries are common but become slow with large result sets because MySQL builds a temporary table for the inner query, consuming CPU and I/O.

Example of a slow subquery:

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

EXPLAIN shows the inner query uses the primary key index, but the outer query still performs a full scan.

Optimized rewrite using a join:

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

This eliminates the temporary table and speeds up execution.

4. ORDER BY Optimization

MySQL can sort using two methods:

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

Using index : scans an ordered index directly; faster.

Goal: make ORDER BY use an index.

4.1 Single‑column sort

When sorting by age without an index, MySQL uses filesort. Adding an index on age switches to index‑based sorting.

4.2 Multi‑column sort

Create a composite index matching the ORDER BY column order (e.g., stuno, age). The left‑most prefix rule must be respected; otherwise filesort is used.

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

Changing the sort direction (ASC vs DESC) for any column also forces filesort.

5. GROUP BY Optimization

GROUP BY shares many principles with ORDER BY:

Indexes can be used even without a WHERE filter.

Follow the left‑most prefix rule for composite indexes.

Increase sort_buffer_size if necessary.

Prefer WHERE over HAVING when possible.

Avoid ORDER BY/GROUP BY on large result sets; keep rows under ~1,000 when feasible.

Adding indexes to GROUP BY columns dramatically reduces execution time, as shown by EXPLAIN screenshots.

6. COUNT Optimization

The COUNT() aggregate scans rows one by one. Using COUNT(*) or COUNT(1) is generally faster than counting a specific column, especially when the column can be NULL.

Best practice: prefer COUNT(*) for row counting.

Practical Test Data

Three test tables were prepared:

student (500,000 rows)

class (10,000 rows)

account (100,000 rows)

A stored procedure addMyData() populates a test table with 100,000 rows:

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;

CALL addMyData();

All images referenced in the original article are retained to illustrate query plans and performance charts.

Conclusion

Effective MySQL performance tuning relies on proper indexing, avoiding large offsets, rewriting subqueries as joins, and ensuring ORDER BY/GROUP BY can leverage indexes. Applying these strategies yields measurable reductions in query latency across pagination, joins, sorting, grouping, and counting operations.