Very slow search query on large wordpress site
Giovanni last edited by 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?
wordlike "matisse%" OR w1.
Now the plugin make a LEFT JOIN for each search term:
LEFT JOIN ( select .... where (w1.
wordlike "matisse%") group by ds1.id order by NULL ) t1 on t1.id = tbase.id
LEFT JOIN ( select ... where (w1.
wordlike "picasso%") group by ds1.id order by NULL ) t2 on t2.id = tbase.id
EpsilonAdmin last edited by
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!