Hi!
I managed to find the cause of the problem, it wasn’t easy 🙂
The queries work perfectly fast, even though there are over 1 million keywords, luckily this was not an issue. There were 3 problems:
1. On the search settings page the product custom post type was not selected, because woocommerce was disabled. After enabling the I was able to add the product custom post type, but there was still no response.
I enabled the debug mode in your wp-config.php file and temporary increased the memory limit to 1GB to see what error message is shown.
There was still no error, but after a step-by step debugging I found the cause, which is the second issue:
2. The search tried to get the authors of the post, but most likely because of the database size, the wordpress internal function “get_users()” failed. It took over 60 seconds to get 10 users by IDs. I’m not sure if this is some kind of bug within wordpress Core, but it looks like it.
Anyways, to solve the problem, I simply disabled this part of the code, so the author is no longer shown in the results list.
3. A minor issue was that the index table was build 25 post at a time (as default option) and at some point it failed. But since your server is extremely powerful, I increased this number 2000, so it build the index without issues in 2 minutes.
Now the search works without problems. Make sure you test ever single page before production, because the “get_users()” function is used widely by plugin developers – so it’s a potential source of failure in other plugins as well.
Plus one more suggestion: I recommend not using the frontend search settings at all with a database size like yours. The category filtering especially. It requires a table join operation, which is fine on smaller databases, but on yours it may cause performance spikes.
Let me know if you need any more help.
Best,Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)
