Description:
The ORDER BY is incorrect, it's sorting by `id`, not the `created` datetime.
SELECT
ae.id,
ae.created,
GROUP_CONCAT(a.id),
COUNT(ae.id) OVER() AS c
FROM
account_extra AS ae
LEFT JOIN
account AS a ON a.id = ae.client_id
GROUP BY
ae.id
ORDER BY
ae.created ASC
+----+---------------------+--------------------+---+
| id | created | GROUP_CONCAT(a.id) | c |
+----+---------------------+--------------------+---+
| 1 | 2026-01-01 01:00:00 | 1 | 4 |
| 2 | 2026-01-01 04:00:00 | 1 | 4 |
| 3 | 2026-01-01 02:00:00 | 2 | 4 |
| 4 | 2026-01-01 03:00:00 | 2 | 4 |
+----+---------------------+--------------------+---+
EXPLAIN FORMAT=TREE ...
-> Window aggregate with buffering: count(ae.id) OVER () (cost=0 rows=0)
-> Table scan on <temporary> (cost=2.5..2.5 rows=0)
-> Temporary table (cost=0..0 rows=0)
-> Group aggregate: group_concat(`account`.id separator ',')
-> Sort: ae.id
-> Stream results (cost=2.05 rows=4)
-> Nested loop left join (cost=2.05 rows=4)
-> Sort: ae.created (cost=0.65 rows=4)
-> Table scan on ae (cost=0.65 rows=4)
-> Single-row covering index lookup on a using PRIMARY (id = ae.client_id) (cost=0.275 rows=1)
Note the "Sort: ae.id" after "Sort: ae.created".
How to repeat:
CREATE TABLE account (
id int NOT NULL AUTO_INCREMENT,
name tinytext COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE account_extra (
id int NOT NULL AUTO_INCREMENT,
client_id int NOT NULL,
created datetime NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO account VALUES (1, 'A');
INSERT INTO account VALUES (2, 'B');
INSERT INTO account_extra VALUES (1, 1, '2026-01-01 01:00:00');
INSERT INTO account_extra VALUES (2, 1, '2026-01-01 04:00:00');
INSERT INTO account_extra VALUES (3, 2, '2026-01-01 02:00:00');
INSERT INTO account_extra VALUES (4, 2, '2026-01-01 03:00:00');
Description: The ORDER BY is incorrect, it's sorting by `id`, not the `created` datetime. SELECT ae.id, ae.created, GROUP_CONCAT(a.id), COUNT(ae.id) OVER() AS c FROM account_extra AS ae LEFT JOIN account AS a ON a.id = ae.client_id GROUP BY ae.id ORDER BY ae.created ASC +----+---------------------+--------------------+---+ | id | created | GROUP_CONCAT(a.id) | c | +----+---------------------+--------------------+---+ | 1 | 2026-01-01 01:00:00 | 1 | 4 | | 2 | 2026-01-01 04:00:00 | 1 | 4 | | 3 | 2026-01-01 02:00:00 | 2 | 4 | | 4 | 2026-01-01 03:00:00 | 2 | 4 | +----+---------------------+--------------------+---+ EXPLAIN FORMAT=TREE ... -> Window aggregate with buffering: count(ae.id) OVER () (cost=0 rows=0) -> Table scan on <temporary> (cost=2.5..2.5 rows=0) -> Temporary table (cost=0..0 rows=0) -> Group aggregate: group_concat(`account`.id separator ',') -> Sort: ae.id -> Stream results (cost=2.05 rows=4) -> Nested loop left join (cost=2.05 rows=4) -> Sort: ae.created (cost=0.65 rows=4) -> Table scan on ae (cost=0.65 rows=4) -> Single-row covering index lookup on a using PRIMARY (id = ae.client_id) (cost=0.275 rows=1) Note the "Sort: ae.id" after "Sort: ae.created". How to repeat: CREATE TABLE account ( id int NOT NULL AUTO_INCREMENT, name tinytext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id) ); CREATE TABLE account_extra ( id int NOT NULL AUTO_INCREMENT, client_id int NOT NULL, created datetime NOT NULL, PRIMARY KEY (id) ); INSERT INTO account VALUES (1, 'A'); INSERT INTO account VALUES (2, 'B'); INSERT INTO account_extra VALUES (1, 1, '2026-01-01 01:00:00'); INSERT INTO account_extra VALUES (2, 1, '2026-01-01 04:00:00'); INSERT INTO account_extra VALUES (3, 2, '2026-01-01 02:00:00'); INSERT INTO account_extra VALUES (4, 2, '2026-01-01 03:00:00');