Optimize query from view with UNION ALL

Question

So I'm facing a difficult scenario, I have a legacy app, bad written and designed, with a table, t_booking. This app, has a calendar view, where, for every hall, and for every day in the month, shows its reservation status, with this query:

SELECT mr1b.id, mr1b.idreserva, mr1b.idhotel, mr1b.idhall, mr1b.idtiporeserva, mr1b.date, mr1b.ampm, mr1b.observaciones, mr1b.observaciones_bookingarea, mr1b.tipo_de_navegacion, mr1b.portal, r.estado 
FROM t_booking mr1b
LEFT JOIN a_reservations r ON mr1b.idreserva = r.id 
WHERE mr1b.idhotel = '$sIdHotel' AND mr1b.idhall = '$hall' AND mr1b.date = '$iAnyo-$iMes-$iDia' 
  AND IF (r.comidacena IS NULL OR r.comidacena = '', mr1b.ampm = 'AM', r.comidacena = 'AM' AND mr1b.ampm = 'AM') 
  AND (r.estado <> 'Cancelled' OR r.estado IS NULL OR r.estado = '') 
LIMIT 1;

(at first there was also a ORDER BY r.estado DESC which I took out)

This query, after proper (I think) indexing, takes 0.004 seconds each, and the overall calendar view is presented in a reasonable time. There are indexes over idhotel, idhall, and date.

Now, I have a new module, well written ;-), which does reservations in another table, but I must present both types of reservations in same calendar view. My first approach was create a view, joining content of both tables, and selecting data for calendar view from this view instead of t_booking.

The view is defined like this:

CREATE OR REPLACE VIEW 
t_booking_hall_reservation
AS 
SELECT id, 
        idreserva, 
        idhotel, 
        idhall, 
        idtiporeserva, 
        date, 
        ampm, 
        observaciones, 
        observaciones_bookingarea, 
        tipo_de_navegacion, portal
    FROM t_booking
UNION ALL
SELECT HR.id, 
        HR.convention_id as idreserva, 
        H.id_hotel as idhotel, 
        HR.hall_id as idhall, 
        99 as idtiporeserva, 
        date, 
        session as ampm,
        observations as observaciones,
        'new module' as observaciones_bookingarea, 
        'events' as tipo_de_navegacion, 
        'new module' as portal
FROM new_hall_reservation HR
JOIN a_halls H on H.id = HR.hall_id
;

(table new_hall_reservation has same indexes)

I tryed UNION ALL instead of UNION as I read this is much more efficient.

Well, the former query, changing t_booking for t_booking_hall_reservation, takes 1.5 seconds, to multiply for each hall and each day, which makes calendar view impossible to finish.

The app is spaguetti code, so, looping twice, once over t_booking and then over new_hall_reservation and combining results is somehow difficult.

Is it possible to tune the view to make this query fast enough? Another approach?

Thanks

PS: the less I modify original query, the less I'll need to modify the legacy app, which is, at less, risky to modify

Answer 1

This is too long for a comment.

A view is (almost) never going to help performance. Yes, they make queries simpler. Yes, they incorporate important logic. But no, they don't help performance.

One key problem is the execution of the view -- it doesn't generally take the filters in the overall tables into account (although the most recent versions of MySQL are better at this).

One suggestion -- which might be a fair bit of work -- is to materialize the view as a table. When the underlying tables change, you need to change t_booking_hall_reservation using triggers. Then you can create indexes on the table to achieve your performance goals.'