CODEWITHSUNDEEP
phpmysql
user7703861
user7703861

Reputation:

MySQL - need better solution for query, extrem load time

I've got 2 tables -> apartments and availabilities.

In table apartments there are all informations about a apartment. In table availabilities there are all informations about the availability for a apartment for the next 365 days.

Table apartments:

id, name

Example:

1, Apartment 1
2, Apartment 2


Table availabilities:

id, apartment_id, date, status

Example:

1, 1, 2017-06-01, free
2, 1, 2017-06-02, booked
3, 1, 2017-06-03, free
4, 1, 2017-06-04, free
5, 1, 2017-06-05, free
...
6, 2, 2017-06-05, free
7, 2, 2017-06-05, free
8, 2, 2017-06-05, free
9, 2, 2017-06-05, booked
10, 2, 2017-06-05, free
...


Now i want search free apartments for a week in a multiple date range like:

SELECT apartments where id in (
    SELECT apartment_id 
    FROM availabilities 
    WHERE EXISTS (
        SELECT apartment_id 
        FROM availabilities as a 
        WHERE availabilities.apartment_id = a.apartment_id 
        AND date >= "2017-06-01" 
        AND date < "2017-06-08" 
        AND status = "free" 
        GROUP BY apartment_id 
        HAVING COUNT(apartment_id) = DATEDIFF("2017-06-08", "2017-06-01")) 
    OR EXISTS (
        SELECT apartment_id 
        FROM availabilities as a 
        WHERE availabilities.apartment_id = a.apartment_id 
        AND date >= "2017-06-02" 
        AND date < "2017-06-09" 
        AND status = "free" 
        GROUP BY apartment_id HAVING COUNT(apartment_id) = DATEDIFF("2017-06-09", "2017-06-02"))`

This should lookup for all possibilities for a week in a date range (2017-06-01 - 2017-06-30)

My problem here is, we got many apartments and this query has a extreme load time.

Any ideas for a better solution?

Upvotes: 0

Views: 63

Answers (1)

user7703861
user7703861

Reputation:

Fixed it with (COUNT(*) and filter for status = "booked") = 0

Query is 30% faster.

Upvotes: 0

Related Questions