Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

6
  • Thank you for your extensive answer, Rick! When using your suggested code, one is not able to filter on flight_services/hotel_services anymore. I've tried it anyways and set your suggested indexes: Surprisingly the query still takes 300 ms to complete. The only difference is that I have connected the WHERE EXISTS' with OR instead of AND (WHERE EXISTS(...) OR EXISTS(...)), because that is how it is supposed to work. (And again, changing it to AND reduces the time to about 5 ms, but breaks functionality.) Why is OR so slow here? Commented Jan 5, 2023 at 9:00
  • @quick-brown-fox - Ouch. OR is deadly for performance. (I may have missed "OR" when I wrote my answer.) I'll suggest turning OR into UNION; come back in a few minutes. Commented Jan 5, 2023 at 19:34
  • Thanks for your update! To answer your question: I'd like to show only bookings that have at least one flight service and/or at least one hotel service (with the 82 in their respective pivot tables). And your UNION query does just that and is incredibly fast (5 ms)! However, filtering on both service tables is still not possible. To solve that, I've added both LEFT JOIN from my initial query. However, this makes it possible to get the same booking multiple times again, so I re-added that GROUP BY. But that does not seem to impact the execution time. Or do you have a better approach? Commented Jan 5, 2023 at 21:46
  • @quick-brown-fox - I assumed that UNION DISTINCT would avoid the need for the GROUP BY. I'm running out of ideas. I hope you understand LEFT JOIN, UNION, etc and can experiment with other options. Commented Jan 5, 2023 at 22:36
  • To make it clear what I mean with filtering: It should be possible to add WHERE conditions on flight_services and hotel_services on the outer query. But it works with said LEFT JOINs and GROUP BY from my initial query. Thank you very much for your help! Commented Jan 6, 2023 at 9:39