Date availability search performance

[notesow]

Hi I took a VPS with 4vCore and 8Gb of ram and the problem persists, I think it’s a configuration problem.

What are the recommended parameters for the SQL and others if needed

[andrii]

Hi,

​Please send more details about the issue, and we will do our best to help you.

[notesow]

@andrii
I have created two parallel websites for testing purposes:

www.timiboat.com
www.timiboat.net

Currently, I am working on timiboat.net. On the homepage, there is a search form that works normally unless a date is entered.

As soon as a date is selected, it takes a very long time to perform the search, but only when a date is entered. I switched to a VPS with 4vCores and 8GB of RAM, but the result did not change. I have tried everything, and the client is extremely frustrated with me at this point. This issue is causing quite a lot of problems for our company.

If you visit the website and try to perform a search first without a date and then with a date, you will notice the significant difference in the time it takes to execute the search.

[notesow]

Now I’m trying to make changes to the plugin’s queries.

[notesow]

I have tried with different locations, and obviously, by trying:

• Palermo and Naples, which have few boats, it takes a maximum of 5 seconds
• with Palau, it takes a long time.

[notesow]

@andrii
The problem only arises with the search because:
If I perform the search without a date: it’s fast.
If I perform the search with the date for Palermo: it’s fast.
If I perform the search without a date for Palermo: it’s fast.
If I perform the search with the date for Naples or elsewhere: it’s fast.
If I perform the search without a date for Naples or elsewhere: it’s fast.
If I click on the “Discover Boats” option in the menu: it’s fast.
If I perform the search without a date for Palau: it’s fast.
If I perform the search with a date for Palau: the entire website freezes and takes a whole minute to load.

Let’s say that it finds 4/5 boats in Palermo, Naples, and so on,
while it finds 8 in Palau, so there isn’t much data to search, but the system freezes with Palau.

[ihor]

Thanks for the detailed testing and reports, yes - if you search by both location and dates the query is pretty complex since it performs a radius-based search (by latitude/longitude) and date availability search (by comparing date ranges). We plan to optimize it in future updates by auto-generating lookup tables instead of querying the wp_posts one.

Please make sure that there are no limitations on the query complexity on your server (database), if some locations work fine and others don’t, this may be the reason.