MySQL Window Function Performance: Sorting, Indexes

Window functions are powerful, and they do real work: sorting, grouping and a pass over each partition. Knowing what they cost lets you write fast queries.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 20000;

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    happened_at DATETIME NOT NULL,
    amount DECIMAL(8,2) NOT NULL
);
INSERT INTO events (user_id, happened_at, amount)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT 1 + MOD(i * 7, 200), '2026-01-01 00:00:00' + INTERVAL i MINUTE, 10 + MOD(i * 13, 500) FROM n;
ANALYZE TABLE events;

What a window costs

For each distinct window (partition + order), MySQL must process the rows sorted by the partition columns and then the order columns:

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT user_id, happened_at, SUM(amount) OVER (PARTITION BY user_id ORDER BY happened_at) AS running FROM events;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | events | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9991 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set

type: ALL reads the whole table, and Using filesort means the rows are sorted for the window. On 10,000 rows this is instant. On 100 million rows it is the cost to watch. (The rows value is an estimate and varies, so the plan blocks in these examples are not compared.)

What an index changes (and what it does not)

An index on the partition and order columns helps in two real ways. It can be covering (it also contains the aggregated column), so MySQL reads a narrow, already ordered index instead of the wide table. And it lets a WHERE on the partition column read only that partition’s rows.

query.sql

SQL

1
2
3

CREATE INDEX idx_user_time_amount ON events (user_id, happened_at, amount);
EXPLAIN FORMAT=TRADITIONAL SELECT user_id, happened_at, SUM(amount) OVER (PARTITION BY user_id ORDER BY happened_at) AS running FROM events;
EXPLAIN FORMAT=TRADITIONAL SELECT user_id, happened_at, SUM(amount) OVER (PARTITION BY user_id ORDER BY happened_at) AS running FROM events WHERE user_id = 7;

Output

OUTPUT

+----+-------------+--------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | events | NULL       | index | NULL          | idx_user_time_amount | 13      | NULL | 9991 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+-------+---------------+----------------------+---------+------+------+----------+-----------------------------+
1 row in set

+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | events | NULL       | ref  | idx_user_time_amount | idx_user_time_amount | 4       | const |   50 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+-----------------------------+
1 row in set

Both plans now say Using index (no table lookups). The second one is type: ref with about 50 rows: only the rows of user 7 are read. Notice that Using filesort is still shown. In the MySQL 8.0, 8.4 and 9.7 servers used to test this course, the window step reports a filesort even when the index delivers the rows in the right order. So do not expect a window query to lose its Using filesort line. Judge a window query by how many rows reach the window and how wide they are, and measure with EXPLAIN ANALYZE (EXPLAIN ANALYZE).

Filter before the window

The window runs on the rows that reach it. WHERE runs before it, so a selective WHERE makes the window much cheaper:

query.sql

SQL

SELECT user_id, happened_at, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY happened_at) AS running
FROM events
WHERE user_id = 7 AND happened_at >= '2026-01-05' AND happened_at < '2026-01-06'
ORDER BY happened_at
LIMIT 3;

Output

OUTPUT

+---------+---------------------+--------+---------+
| user_id | happened_at         | amount | running |
+---------+---------------------+--------+---------+
|       7 | 2026-01-05 01:38:00 | 164.00 |  164.00 |
|       7 | 2026-01-05 04:58:00 | 264.00 |  428.00 |
|       7 | 2026-01-05 08:18:00 | 364.00 |  792.00 |
+---------+---------------------+--------+---------+
3 rows in set

But think about the meaning: a WHERE on happened_at removes the earlier rows, so the running total starts at the beginning of the filtered range, not at the true start. If the total should include earlier rows, compute the window first (in a CTE), and filter the outer query:

query.sql

SQL

WITH running AS (
    SELECT user_id, happened_at, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY happened_at) AS running_total FROM events WHERE user_id = 7
)
SELECT happened_at, amount, running_total FROM running WHERE happened_at >= '2026-01-05' AND happened_at < '2026-01-06' ORDER BY happened_at LIMIT 3;

Output

OUTPUT

+---------------------+--------+---------------+
| happened_at         | amount | running_total |
+---------------------+--------+---------------+
| 2026-01-05 01:38:00 | 164.00 |       7920.00 |
| 2026-01-05 04:58:00 | 264.00 |       8184.00 |
| 2026-01-05 08:18:00 | 364.00 |       8548.00 |
+---------------------+--------+---------------+
3 rows in set

Filter on the partition key early (user_id = 7), and on the order key late. With the covering index above, the filter on user_id is a cheap index lookup.

The cost of frames

ROWS UNBOUNDED PRECEDING ... CURRENT ROW (running totals) and moving SUM/AVG are computed incrementally: each row adds one value and removes one. Cheap.
MIN/MAX over a moving frame, and large RANGE frames, can cost more.
A frame that spans the whole partition, for every row, is calculated once per partition by MySQL.

Several windows: several sorts

Each different window specification needs its own sort. Two functions with the same PARTITION BY and ORDER BY share it. Use named windows and keep the specifications equal (Named Windows).

Restrictions and errors

Limitation	Example
Not allowed in `WHERE`, `GROUP BY`, `HAVING`	`WHERE ROW_NUMBER() OVER (...) = 1` fails. Use a derived table or CTE
Not allowed in the recursive part of a recursive CTE
`DISTINCT` is not supported inside most window aggregates	`COUNT(DISTINCT x) OVER (...)` gives an error
`RANGE` with a value offset needs one numeric or temporal `ORDER BY` column
No `IGNORE NULLS` for `LAG`, `FIRST_VALUE` etc.
Window functions cannot be nested directly	`SUM(ROW_NUMBER() OVER (...)) OVER (...)` is an error

query.sql

SQL

SELECT user_id, COUNT(DISTINCT amount) OVER (PARTITION BY user_id) FROM events LIMIT 1;

Output

OUTPUT

ERROR 1235 (42000): This version of MySQL doesn't yet support '<window function>(DISTINCT ..)'

Alternatives when a window is too slow

Pre-compute with a summary table (Summary Tables).
Reduce the rows before the window, with a WHERE or a pre-aggregation by day.
Use LATERAL for top-N per group on huge tables.
Do the running totals in the application when the report is small and the logic is complicated.
Paginate: a window over a very large result and then LIMIT 20 still has to process everything first.

Check the plan and the time

query.sql

SQL

EXPLAIN FORMAT=TREE SELECT user_id, SUM(amount) OVER (PARTITION BY user_id ORDER BY happened_at) FROM events;

Output

OUTPUT

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Window aggregate with buffering: sum(`events`.amount) OVER (PARTITION BY `events`.user_id ORDER BY `events`.happened_at )   (cost=0 rows=9991)
    -> Sort: `events`.user_id, `events`.happened_at  (cost=1005 rows=9991)
        -> Table scan on events  (cost=1005 rows=9991)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

EXPLAIN ANALYZE shows the real time per step (EXPLAIN ANALYZE).

lightbulb

Think about the input

For a slow window query, ask: “How many rows reach the window, and how wide are they?” Filter early on the partition key, select only the columns you need and use a covering index.