Very slow search query on large wordpress site

  • 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 order by NULL ) t1 on =
    LEFT JOIN ( select ... where (w1.word like "picasso%") group by order by NULL ) t2 on =
    where ....


  • 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!

  • 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.

Log in to reply