Why GROUP BY + ORDER BY Crawls on 100M-Row MySQL Tables Despite Index Usage

01 EXPLAIN Indicators: Using temporary and Using filesort

When running aggregation queries on hundred-million-row tables, first check EXPLAIN output for Using temporary and Using filesort appearing together.

EXPLAIN SELECT city, COUNT(*) as cnt
FROM orders
WHERE create_time > '2025-01-01'
GROUP BY city
ORDER BY cnt DESC;

Using temporary

means MySQL creates a temporary table to hold intermediate grouping results. Using filesort means sorting cannot use an index and requires an extra sort pass in memory or on disk. With small data volumes this is unnoticeable, but at hundred-million-row scale the temporary table exceeds tmp_table_size (default 16 MB), spills to disk as a MyISAM temporary table, and disk I/O for aggregation plus sorting explodes.

02 GROUP BY and ORDER BY Column Mismatch Prevents Index Use

MySQL has an optimization called Loose Index Scan : if the GROUP BY column is a prefix of an index, MySQL can scan the index in order, jumping to the first record of each group, avoiding a temporary table.

Example: with a composite index (create_time, city), the query below can use the index for both filtering and ordering:

SELECT city, COUNT(*)
FROM orders
WHERE create_time > '2025-01-01'
GROUP BY city ORDER BY city;

The WHERE clause narrows the scan via create_time; city is already ordered in the index, so GROUP BY leverages that order and ORDER BY city needs no extra sort. EXPLAIN shows neither Using temporary nor Using filesort.

However, production demands often require GROUP BY city ORDER BY COUNT(*) DESC. The sort column ( COUNT(*)) is computed after aggregation and does not exist in the index. MySQL must first materialize all groups in a temporary table, then sort the result set. The index only helps reduce scanned rows via the WHERE filter; grouping and sorting remain slow.

03 Even Perfect Index Cannot Overcome Billion-Row Scan Volume

Assume GROUP BY and ORDER BY columns match and the index is perfect — no temporary table, no filesort. Is it fast? Not necessarily.

Indexes solve how to find rows, not how many rows. If the WHERE filter still leaves 50 million rows to aggregate, MySQL must scan 50 million index entries, performing an aggregation accumulation per row. InnoDB B+ tree leaf pages are 16 KB; 50 million index entries require reading hundreds of thousands of pages. Even in the best case this takes several to tens of seconds — far above a typical 500 ms SLA. Index tuning alone cannot bridge that gap.

04 Don't Run Real-Time Aggregation on Large Tables

The reliable production approach for hundred-million-row aggregations is pre-computation : compute results ahead of time and store them in a small summary table.

Simple scheduled summary table: Run a nightly or hourly job to populate a daily summary.

CREATE TABLE order_daily_summary (
  stat_date DATE,
  city VARCHAR(50),
  order_count INT,
  total_amount DECIMAL(12,2),
  PRIMARY KEY (stat_date, city)
) ENGINE=InnoDB;

-- Run every night
INSERT INTO order_daily_summary (stat_date, city, order_count, total_amount)
SELECT DATE(create_time), city, COUNT(*), SUM(amount)
FROM orders
WHERE create_time >= CURDATE() - INTERVAL 1 DAY
  AND create_time < CURDATE()
GROUP BY DATE(create_time), city;

The summary table holds only thousands to tens of thousands of rows. Front-end queries hit this table; GROUP BY and ORDER BY return in milliseconds. The large table is scanned only once during off-peak hours, not affecting daytime online traffic.

Real-time incremental update: If fresher data is needed, update the summary table on each order insert.

INSERT INTO order_daily_summary (stat_date, city, order_count, total_amount)
VALUES (CURDATE(), '北京', 1, 99.00)
ON DUPLICATE KEY UPDATE
  order_count = order_count + 1,
  total_amount = total_amount + 99.00;

Each order write incurs one extra increment, but for reporting scenarios this trade-off is well worth it.

05 How Much Can Partitioning Help?

MySQL RANGE partitioning by time splits the table; a query for the last month scans only one partition instead of the whole table.

ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  PARTITION p202503 VALUES LESS THAN (TO_DAYS('2025-04-01'))
);

With a time condition, MySQL performs partition pruning , reading only the target partition. One hundred million rows split into 12 monthly partitions yields ~8 million rows per partition, reducing aggregation work by an order of magnitude.

Crucial prerequisite: the WHERE clause must include the partition key . If the business needs a full-history grouping by city without a time filter, partition pruning does not activate and a full table scan occurs.

06 High-Cardinality GROUP BY

Another scenario: GROUP BY column has very high cardinality, e.g., user_id with tens of millions of distinct users. The grouping result itself has tens of millions of rows; the temporary table grows to several gigabytes. No index optimization helps because the intermediate result set is too large for MySQL's row-oriented storage engine, which is fundamentally unsuited for large-scale analytics.

For such workloads, switch engines. ClickHouse is a column-oriented database designed for aggregation analytics. The same hundred-million-row GROUP BY query may finish in 1–2 seconds. A common architecture: MySQL handles OLTP transactions; data is replicated to ClickHouse via Canal or Debezium; reporting and analytical queries run entirely on ClickHouse. Both systems operate independently without interference.

07 Narrow the Query Scope

After all technical solutions, the most effective lever is often non-technical: talk to product. Default the report page to show only the last 7 days of data instead of the full history. A hundred-million-row table with WHERE create_time > NOW() - INTERVAL 7 DAY may shrink to 2 million rows; combined with a proper index, GROUP BY + ORDER BY returns instantly.

Most users never need full-history aggregates. A default 7-day window plus a date picker satisfies product requirements while cutting database load by one to two orders of magnitude — far more impactful than low-level database tuning.

08 Final Takeaway

The root cause of slow GROUP BY + ORDER BY on hundred-million-row tables is not SQL syntax but MySQL's row-oriented storage engine hitting its natural bottleneck in large-scale aggregation. Indexes improve scan efficiency but cannot reduce the number of rows scanned. Temporary tables and filesort degrade catastrophically with data volume; parameter tuning cannot compensate.

The correct path for hundred-million-row aggregation: either pre-compute results (summary tables), shrink the computation scope (partitioning + time filter), or switch to an engine built for aggregation (ClickHouse). Running real-time GROUP BY ORDER BY on the large table is a dead end.