Hi Boris,
Thank you very much for the details, it helps a lot.
There seems to be a general issue somewhere on the website. When I try to load the products page, it takes ~8 seconds to load, if I enter a search term on the products page on the back-end, then it’s over 30 seconds, so it looks like something might be adding a lot of workload to the server. I suspect that there might be a little too many plugins active and one or more is causing a massive workload for certain tasks. I strongly recommend removing some plugins which you may not use, it can significantly improve the overall site health. If you are using cloudflare enterprise, then I would disable all cache, query monitor and profiler plugins, those are already mitigated by Cloudflare itself.
The index is small, it’s only 10000 keywords, it should execute almost instantly. I did a debugging and found one issue, and that was the primary ordering using the stock status, but that shouldn’t be an issue at all. I changed it to relevance, now some bottleneck is gone and the search is a lot faster. I recommend checking the wp_postmeta table, perhaps do a degfragmentation on it, it seems like something might be wrong there, as all queries on the metadata table seem to be slowed down for some reason, maybe there is a table lock or a paging issue that could be solved via an optimize table commad. I also strongly recommend trying this plugin after you did a table optimize command on the postmeta table.