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



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


Log in to reply