Poor performance when at least one of the left joins should match a record

Question

I've got bookings (bookings) in my database. A booking can have 0 to n flight services (flight_services) and 0 to n hotel services (hotel_services). A user on my website can filter the bookings by setting where conditions on each of these tables.

When SELECTing, only bookings that have at least one flight service or at least one hotel service should be returned. Furthermore, there's a flight_pivot_table and a hotel_pivot_table and only the services should be considered that have a fix id (here 82) in those pivot tables.

The query:

select `bookings`.*
from `bookings`
left join (
    select `flight_services`.*
    from `flight_services`
    inner join `flight_pivot_table`
        on `flight_services`.`id` = `flight_pivot_table`.`flight_service_id`
    where `flight_pivot_table`.`some_id` = 82
) as `flight_services`
    on `bookings`.`id` = `flight_services`.`booking_id`
left join (
    select `hotel_services`.*
    from `hotel_services`
    inner join `hotel_pivot_table`
        on `hotel_services`.`id` = `hotel_pivot_table`.`hotel_service_id`
    where `hotel_pivot_table`.`some_id` = 82
) as `hotel_services`
    on `bookings`.`id` = `hotel_services`.`booking_id`
where (
    flight_services.id is not null or
    hotel_services.id is not null
)
group by `bookings`.`id`

Unfortunately, this is extremely slow, although all indexes are used. With the data I've got, this query takes about 300 ms to execute. Here's the output of EXPLAIN:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	bookings		index	PRIMARY	PRIMARY	4		35173	100
1	SIMPLE	flight_services		ref	PRIMARY,flight_services_uq	flight_services_uq	4	my_db.bookings.id	2	100	Using index
1	SIMPLE	flight_pivot_table		eq_ref	PRIMARY	PRIMARY	8	const,my_db.flight_services.id	1	100	Using index
1	SIMPLE	hotel_services		ref	PRIMARY,hotel_services_uq	hotel_services_uq	4	my_db.bookings.id	1	100	Using where; Using index
1	SIMPLE	hotel_pivot_table		eq_ref	PRIMARY	PRIMARY	8	const,my_db.hotel_services.id	1	100	Using index

One of the following actions reduces the time to about 3 ms but obviously break the functionality:

• Remove the INNER JOIN from one or both of the sub queries.
• Remove the WHERE conditions.
• Replace the or with and in the WHERE conditions.

Notes:

• As it should be possible to add where conditions for flight_services and hotel_services, the aliases given for the left join sub-queries match the table names.
• I use GROUP BY because every booking should returned only once, of course.

How can I accelerate this?

Answer 1

Whenever possible, it is usually better to use JOINs rather than "derived tables". (There are cases where the opposite is faster.)

That is, instead of

SELECT ...
    FROM ( SELECT ... a JOIN b ... )
    JOIN ...

do

SELECT ...
    FROM a
    JOIN b ...
    JOIN ...

That being said, I see that you may have the "explode-implode" case:

SELECT ...
    FROM x
    JOIN z        -- explode
    GROUP BY x.id   -- implode

The JOIN flight_services seems to be 1:many -- hence "explode". But you are not using any of the data from that table??? Why JOIN to it? Why fetch all the columns, only to throw them away?

The pattern

LEFT JOIN z  ON blah
...
WHERE z.id IS NOT NULL

can be replaced by

WHERE EXISTS ( SELECT 1 FROM z WHERE blah )

if you don't actually use any of the data from z (ie, flight_services).

This "semi-join" is faster because it stops once a match is found. It does not do the "inflate", thereby helping obviate the need for the GROUP BY.

Ditto for the other LEFT JOIN.

I'm confused by the JOIN inside your derived tables. Anyway, it can also be done in the SELECT inside the EXISTS.

Here is my suggested code

select  b.*
    from  `bookings` AS b
    WHERE  EXISTS 
        ( SELECT  1
            from  `flight_services` AS fs
            inner join  `flight_pivot_table` AS fpt  ON fs.`id` = fpt.`flight_service_id`
            where  fpt.`some_id` = 82
              AND  b.`id` = fs.`booking_id` 
        ) 
      AND EXISTS (... hotel_services join ...)
      -- no WHERE

Suggested indexes:

fs:  INDEX(booking_id, id)
fpt:  INDEX(some_id, flight_service_id)

Both the reformulation and the composite indexes should help speed things up.

OR not AND

Oops, I misread the original query. OR is usually an Optimization killer. But rewriting the query to use UNION may be the answer.

( SELECT ...
    WHERE ... -- checking flight_services
) UNION ALL
( SELECT ...
    WHERE ... -- checking hotel
)

So you want to show cases where there are flight services but no hotel services? And vice versa?

(Note: UNION ALL should probably be UNION DISTINCT because there could be the same 'booking' from both?)

Anyway, let's work toward another optimization. Since you want just the bookings and nothing about the flight and hotel, lets start by finding the booking.id values, then reach for bookings.*

This should give you the booking.ids based on flight stuff:

        SELECT  fs.`booking_id` 
            from  `flight_services` AS fs
            inner join  `flight_pivot_table` AS fpt
                ON fs.`id` = fpt.`flight_service_id`
            where  fpt.`some_id` = 82

Then, to finish

SELECT b.*
    FROM (
           ( that query
           )
           UNION DISTINCT
           ( a similar query, but for hotel
           )
         ) AS u
    JOIN bookings AS b  ON u.booking_id = b.id