ORDER BY Performance really slow

SQL++
20

Hi @zeestorm

CREATE INDEX `idx_activity__teamID__public_desc` ON `default`((`team`.`_id`),(-MILLIS(`created_at`)))
WHERE ((`type` = "activity") and (`private` = false));

SELECT a.*
FROM default a USE INDEX (`idx_activity__teamID__public_desc`)
WHERE a.`type` = "activity" AND a.`private` = false AND a.`team`.`_id` = "f51e4695-479d-4ea6-beab-425ad9484ee9"
AND -MILLIS(a.created_at) <= 0
ORDER BY a.`team`.`_id`, -MILLIS(a.created_at)
LIMIT 20;

In 4.5.1 Try above query. added ORDER BY a.team._id, -MILLIS(a.created_at)
In query where clause a.team._id is equality predicate the result will not change. In 4.6.x Query optimizer detects this and takes care of it.

1 Like