MySQL Query Optimization Strategies: Pagination, Joins, Subqueries, Sorting, Group By, and Count

When a system experiences latency or slowdown as data volume grows, developers and architects need to perform database tuning, and SQL optimization remains a crucial part of that effort.

Pagination Query Optimization

Large offset values cause MySQL to sort and discard many rows, leading to high cost. Two common solutions are:

Use a covering index with a sub‑query to fetch only the primary keys, then join back to the main table.

For auto‑increment primary keys, replace LIMIT offset, count with a range condition on the primary key.

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

SELECT * FROM student WHERE id > 400000 LIMIT 10;

Join Query Optimization

Adding appropriate indexes to join columns dramatically improves performance. For left/right joins, ensure the driving table is the smaller one and that join columns are indexed.

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

After indexing tenant_name and account , the EXPLAIN output shows a shift from full table scans to indexed lookups.

Subquery Optimization

Subqueries often create temporary tables, consuming CPU and I/O. Rewriting them as joins avoids this overhead.

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

Optimized version:

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

ORDER BY Optimization

MySQL can sort using either Using filesort (slow) or Using index (fast). Adding indexes on the ordered columns enables the latter.

CREATE INDEX idx_stuno_age ON student(stuno, age);

When the index order does not match the ORDER BY clause or when mixed ASC/DESC is used, MySQL falls back to filesort .

GROUP BY Optimization

Group‑by can also benefit from indexes. Follow the left‑most prefix rule, keep result sets small, and prefer WHERE over HAVING when possible.

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

COUNT() Optimization

Different forms of COUNT() have varying performance. The most efficient is COUNT(*) , followed by COUNT(1) , then COUNT(primary_key) , and finally COUNT(column) .

Use COUNT(*) whenever possible for best speed.

Overall, applying appropriate indexes, rewriting subqueries as joins, and respecting MySQL’s sorting and grouping rules can significantly reduce query execution time and improve system responsiveness.