SQL Query Optimization: Sorting, GROUP BY, Pagination, and Index Strategies

In large‑scale scenarios, SQL query performance, especially for ORDER BY, GROUP BY and pagination, becomes critical. This article delves into optimization techniques, explaining the difference between FileSort and Index sorting in MySQL, and provides practical advice on when and how to add indexes on WHERE and ORDER BY columns.

Sorting optimization – Discusses why indexes are needed on ORDER BY even if indexed in WHERE, describes FileSort vs Index sort, and gives recommendations such as using composite indexes that match the ORDER BY clause. Example:

CREATE INDEX idx_age_name ON student(age, NAME);

Shows EXPLAIN outputs for various ORDER BY cases, illustrating when indexes are used or ignored, the impact of direction (ASC/DESC), and the effect of LIMIT on index usage.

GROUP BY optimization – Highlights that GROUP BY can use indexes similarly to ORDER BY, recommends using left‑most prefix indexes, and advises keeping result sets small (under 1000 rows) to avoid heavy CPU usage.

Pagination optimization – Addresses the cost of large OFFSET values and suggests using index‑based pagination or subqueries to fetch only needed rows, e.g.:

SELECT * FROM student t, (SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

Also mentions adjusting sort_buffer_size and max_length_for_sort_data to influence the choice between single‑pass and double‑pass sorting algorithms.

Covering indexes – Explains the concept, benefits (avoiding back‑table lookups, reducing I/O), and provides examples of creating covering indexes and their effect on EXPLAIN plans.

Prefix indexes – Describes how prefix indexes save space but may prevent covering index usage, with a comparison of full‑string vs prefix index queries.

Overall, the article provides a comprehensive guide to improving MySQL query performance through careful index design, query rewriting, and server configuration tweaks.