Comprehensive MySQL SQL Optimization Strategies and Practical Examples

Preface

After a program has been running for a while and the data volume grows, developers often notice latency and stutter, which requires system tuning. Among many tuning methods, SQL optimization remains a crucial part; the following examples summarize common SQL optimization strategies.

Query Optimization

In most systems, reads far outnumber writes, making query‑related SQL very frequent.

Preparation: add 100,000 rows to a test table.

Use the following stored procedure to generate data (replace the table name with your own):

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 ;

Then call the procedure:

call addMyData();

Three tables are prepared for testing: student (500k rows), class (10k rows), and account (100k rows).

1. Pagination Query Optimization

When the offset is large, MySQL must sort a huge number of rows, e.g., limit 400000,10 , which is very costly.

Optimization Idea:

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

Example:

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

Another approach for auto‑increment primary keys:

select * from student where id > 400000 limit 10;

2. Join Query Optimization

Indexes on join columns are essential. Below are several scenarios.

2.1 Left/Right Join

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 index usage on class . Optimization suggestions:

Make the query fields covered by a primary or covering index.

Prefer pagination when possible.

2.2 Index on Join Columns (Left Join)

When the join column is not a primary key, add indexes:

create index idx_name on tenant(tenant_name);
create index idx_account on `user`(account);

After adding indexes, EXPLAIN shows the second row type changes to ref and rows are reduced.

2.3 Index on Join Columns (Inner Join)

Remove existing indexes, then add them back selectively to see how the optimizer chooses the driving table.

ALTER TABLE `user` DROP INDEX idx_account;
ALTER TABLE `tenant` DROP INDEX idx_name;

Re‑adding the index on tenant_name while dropping the one on user.account demonstrates that the optimizer may still avoid using indexes depending on its cost model.

3. Subquery Optimization

Large subqueries can be slow because MySQL creates a temporary table that cannot use indexes. Replace subqueries with joins when possible.

Original slow query:

select st.* from student st where st.classId in (
  select id from class where id > 100
);

Optimized join version:

select st.id from student st join class cl on st.classId = cl.id where cl.id > 100;

Key reasons for subquery inefficiency are temporary table creation, inability to use indexes, and large result sets.

Prefer JOIN over NOT IN or NOT EXISTS ; use LEFT JOIN … WHERE … IS NULL instead.

4. Order By Optimization

Two sorting methods:

Using filesort : MySQL reads rows then sorts them in a buffer.

Using index : Ordered index scan returns rows already sorted.

Prefer Using index . Adding an index on the sorting column (e.g., age ) changes the plan from filesort to index.

For multi‑column sorting, create a composite index respecting the left‑most prefix rule; the order of columns and sort direction must match the index definition.

5. Group By Optimization

Group‑by benefits from the same index strategies as order‑by. Use left‑most prefix indexes, avoid large result sets, and prefer WHERE over HAVING when possible.

6. Count Optimization

The COUNT() function scans rows; using COUNT(*) or COUNT(1) is generally faster than counting a specific column. Prefer COUNT(*) when possible.

--- End of Article ---