Very slow search query on large wordpress site

[Giovanni]

I have the last version of plugin (v1.26.67).
The table wpftsi_index have 650K rows
The table wpftsi_vectors have 34M (millions) rows
I use mysql8 on a server with 12 core and 64GB of memory.
When the user make a search with more then 2 or 3 terms the db server hang.
There's a way to simplify the JOIN query and avoid the LEFT JOIN for each search term?

Something like:
where (w1.word like "matisse%" OR w1.word like "picasso%")

Now the plugin make a LEFT JOIN for each search term:
LEFT JOIN ( select .... where (w1.word like "matisse%") group by ds1.id order by NULL ) t1 on t1.id = tbase.id
LEFT JOIN ( select ... where (w1.word like "picasso%") group by ds1.id order by NULL ) t2 on t2.id = tbase.id
where ....

Thanks.
Giovanni.

[EpsilonAdmin]

Hello, it looks like your MySQL is a bit not optimized. With this huge amount of RAM you can sufficiently increase the InnoDB cache size, index size and sort order buffer size which will make search faster.

Unfortunately, there is no magic and for big amount of data and posts, we have to have powerful servers and optimized MySQL config.

I would recommend you to read this article: link text You may Google for more good posts.

Let me know if this helps. Thanks!

[EpsilonAdmin]

Hi, Giovanni

Recently we published a new version of the WPFTS plugin which uses a completely new search algorithm (v 1.30.85). It was built with large database support in mind. Did you try that? Please tell me if you have better speed results with it or not.

It still has some room for improvement (via temporary tables, for example) which will be added in the nearest versions of the plugin.

[jwktje]

I have this issue too. It takes a very long time for a new search to return results. What can I do to improve this? It didn't happen in the past, only recently

[EpsilonAdmin]

@jwktje Which version of WPFTS you are using?

[jwktje]

@EpsilonAdmin 2.36.125

[EpsilonAdmin]

@jwktje Please check if you have "Deep Search" flag set ON at the WPFTS Settings / Search & Output page. If so, try to check it OFF.

Also, there may be some limitations with your hoster (for example they limited CPU resources for you in case your website is too heavy for shared hosting etc).

We have an experimental WPFTS version which makes faster search - we could try this too.

[jwktje]

@EpsilonAdmin For me this fixed it! Thank you very much. Was this deep search recently added in an update by any chance? For me it would have been a smoother upgrade if this was only "on by default" on new installations, not on updated ones. You could hook into the plugin activation hook. Because now it's obvious this is by design and there is a good reason for my slow search results. But if I didn't know any better I would think my site was broken.

[EpsilonAdmin]

@jwktje Actually this flag is disabled by default. We know about the disasters it can make. Should be you or someone else who has an access to admin switched it ON occasionally.

Thank you.

[jwktje]

@EpsilonAdmin That's very possible that someone else (or even I) turned that on in the past not knowing the consequences. Still weird that it only recently introduced the huge slowdown. Whatever the case, I'm very happy that it's now back to acceptable performance. Once again, thanks for your help

[mazcabral]

@EpsilonAdmin
Hi,
I'm trying the same problems and read "We have an experimental WPFTS version which makes faster search - we could try this too."
I would like to be able to try this version because I am experiencing a great loss of performance but WPFTS is very important for my searches.
Thank you!

[EpsilonAdmin]

@mazcabral I have sent a link to your registration email. Could you install this new version and tell me if it fixes the problem for you.

[mazcabral]

@EpsilonAdmin
Great! but I still haven't received the email with the link. If possible, please send me back so that I can install and do the tests. Thank you so much!