vendredi 5 janvier 2018

Is there a way to optimize this scope further?

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