I have this chained scope that produces the following sql. It important that it be ordered created_at desc and by comments with an actual comment, and the null values after that.
It's been optimized to use subqueries instead of joins and that has helped quite a bit, but still not enough.
Ultimately, the bottleneck is with trying to get null values at the end of the results.
This is a mysql db.
I have an index on comment and created_at together
SELECT `table_one`.*
FROM `table_one`
WHERE `table_one`.`status` = 'A'
AND `table_one`.`table_two_id` IN
(SELECT `table_two`.`id`
FROM `table_two`
WHERE `table_two`.`slug` IS NOT NULL
AND `table_two`.`status` = 'R')
AND `table_one`.`table_three_id` IN
(SELECT `table_three`.`table_three_id`
FROM `table_three`
WHERE `table_three`.`status` = 'A'
AND `table_three`.`table_four_id` IN
(SELECT `table_four`.`id`
FROM `table_four`
WHERE `table_four`.`status` = 'A'
AND `table_four`.`table_three_id` = 6))
ORDER BY CASE
WHEN COMMENT IS NULL THEN 1
ELSE 0
END,
table_one.created_at DESC
LIMIT 20
OFFSET 0
this is querying over a million rows and will take ~12 seconds. If I remove any attempt to get the NULLS last it will run reasonably quickly.
Can you see a wat to speed it up?
Aucun commentaire:
Enregistrer un commentaire