Mysql – Query multiple rooms with different conditionals (Adults, Children)

MySQL

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per hotel like this:

1 Room with 2 Adults / 0 Children
1 Room with 4 Adults / 2 Children
1 Room with 2 Adults / 1 Children

Here is my query:

SELECT 
COUNT(pl.day) AS Days, p.property_ID AS Hotel_ID, 
p.name AS Hotel_Name, r.room_name AS Room_Name, r.room_type_ID AS Room_ID

FROM property p

  INNER JOIN room_type r 
    ON p.property_ID=r.property_ID
    AND (r.max_adults >= 3 AND r.max_children >= 0)

  INNER JOIN plan pl 
    ON pl.room_type_ID=r.room_type_ID
    AND (pl.day >= "2014-07-07" AND pl.day <= "2014-07-11")

GROUP BY Room_ID, Hotel_ID
HAVING Days = 4

EDIT:
The tables are:

property > property_ID …
plan > property_ID | room_type_ID | day | rate | alloc | min_stay …
room_type > property_ID | max_adults | max_children …
…

Best Answer

first insert the resultset of the room_type table to a temp table with the filtering by your parameters

 r.max_adults >= 3 AND r.max_children >= 0

then query that table and join the property and plan tables to it and it should be faster.

Related Solutions

Mysql – Optimizing multi-table left joins

PLEASE use SHOW CREATE TABLE; DESCRIBE is not descriptive enough.

You seem to be working with IPv4 IP addresses. IPv6 is upon us!

Your query inherently must do a full table scan in order to get the "total" upon which you do the ORDER BY. Since there are a million rows, it will take time.

count('*')

The quotes are not needed.

The two SELECTs are likely to be identical; order across JOIN does not matter. It does matter across LEFT JOIN. (INNER and OUTER add nothing.)

Suggestion...

First, see if this gives the correct information (albeit incomplet info):

SELECT  e.sid, e.cid, COUNT(*) AS total
FROM  event AS e
JOIN  iphdr as h ON e.sid = h.sid
                AND  e.cid = h.cid
WHERE  e.is_deleted = 0
GROUP BY  i.ip_src, i.ip_dst
ORDER BY  total DESC
LIMIT  5;

If so, then use that as a subquery, a la FROM (that-select) and LEFT JOIN to the other two tables to get the rest of the info.

The trick... The subquery is doing less work since it is touching 2 tables (vs 4). The LEFT JOINs only have to do 5 probes each, not a million.

Thesql : inner join takes 3 minutes

Here is your original query

select 
    a.AsOfDate, b.Flag 
from 
    A a inner Join B b on 
        a.BId = b.Id and b.ValidFrom <= a.AsOfDate and b.ValidTo >= a.AsOfDate
where
    a.AsOfDate >= 20110101 and a.AsOfDate <= 20111231

I would suggest refactoring your query in this instance:

select 
    a.AsOfDate, b.Flag 
from
    (
        select * from A
        WHERE AsOfDate >= 20110101
        AND AsOfDate <= 20111231
    ) a INNER JOIN B b ON a.bid=b.id
    AND b.validfrom <= a.asofdate
    AND b.validto   >= a.asofdate
;

That way, the A side's date range ( 20110101 - 20111231 ) gets handled first before the JOIN. An additional benefit of the refactored query is that the JOIN of A and B involves a smaller subset of A.

If you feel uncomfortable with the refactored query, here is another suggestion: switch the range-based WHERE and JOIN clauses

select 
    a.AsOfDate, b.Flag 
from 
    A a inner Join B b on 
        a.BId = b.Id and a.AsOfDate >= 20110101 and a.AsOfDate <= 20111231
where
    b.ValidFrom <= a.AsOfDate and b.ValidTo >= a.AsOfDate

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Optimizing multi-table left joins

Thesql : inner join takes 3 minutes

Related Question