I have a query that is used to return available rooms for a booking system

Using ACF, I have a custom post type representing reservation with various fields

The plan here is to find all the booked rooms with the subquery, and then get all the available rooms by selecting the posts who are not in the result set returned by the subquery

My reservation custom posts have a “terrain_id” who is supposed to match the post id of the rooms (Camping or cottages)

The first inner join is for the room ID (terrain_id), the second inner join is for the start date, and the last one is for the end date

So basically I match the room_id with the post_id and return all the unavailable rooms for the specified time interval, and then I get all the IDS who aren’t present in the subquery result to get my available rooms

The problem is the following :

This query runs in about 5-6 seconds on a tiny data sample (about 2000 reservations, and 100 rooms)

I tried different ways to optimize it but I am pretty much stuck

There are indexes on all the relevant fields

The subquery itself runs in about 0.030 seconds so I don’t think the inner joins are the issues here, the bulk of the problem seems to be in SELECT where NOT EXISTS

Using default wordpress methods and the loop is not option since it’s not possible to do what is need and I would need to query all the custom fields individually for each records

How can I improve my query performance?

How can select basic attributes where not present in an array of about 100 records can take so long?

SELECT wps.ID, wps.post_title, wps.post_type, wps.post_status
FROM wp_posts wps, wp_postmeta wpm
WHERE NOT EXISTS
(
    SELECT
      p.ID,
      pm_ti.meta_value as tiv,
      pm_ti.meta_key as tik,
      pm_ed.meta_key as edk,
      pm_ed.meta_value as edv,
      pm_sd.meta_key as sdk,
      pm_sd.meta_value as sdv
    FROM
      wp_posts p
    INNER JOIN wp_postmeta pm_ti ON (pm_ti.post_id = p.ID )
    INNER JOIN wp_postmeta pm_ed ON (pm_ed.post_id = p.ID )
    INNER JOIN wp_postmeta pm_sd ON (pm_sd.post_id = p.ID )
    WHERE NOT (pm_ed.meta_value <= '{$start}' OR pm_sd.meta_value >= '{$end}')
    AND pm_ti.meta_key='terrain_id' and p.post_status='publish' and wps.ID = pm_ti.meta_value and pm_ed.meta_key='e_date' and pm_sd.meta_key='s_date'
    GROUP BY p.ID
)
AND wpm.post_id = wps.ID AND wps.post_status='publish' AND wps.ID = icl_translations.element_id
and (wps.post_type='camping' or wps.post_type='cottages') GROUP BY wps.ID
");

Read more here: How can I improve a slow custom query? (subquery + inner joins with custom fields)


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.

Related Wordpress search:

, , , , , ,

Wordpress related questions and answers: