News for hackers. Stuff that matters.
Re:Speeding up SQL queries by orders of magnitude using UNION by zeroimpl on Sunday March 21, 2021 @12:03PM (#26531380)
I suspect query #2 would have performed better if they used a COALESCE instead of an OR condition. One of the most important optimization lessons you can learn is not to use OR for any join conditions.

Eg instead of this:

    ON (customer_order_items.meal_item_id = meal_items.id
        OR employee_markouts.meal_item_id = meal_items.id
       )
Do this:

    ON meal_items.id = COALESCE( employee_markouts.meal_item_id, customer_order_items.meal_item_id )
The latter can optimize properly. Probably still slower than the UNION, but would be more in line with expected performance. It might be more useful too in certain cases, such as if building a view.

There are no comments on this comment yet.