mysql - Slow search queries with a large database

admin管理员组

文章数量:1122832

I am working on a website csol.cz where the search takes 5-20 seconds depending on the length of the query and whether it has special characters.

The server is a VPS with two CPU cores and 8 GB RAM, running Debian 12.

I have tried both page cache via WP Fastest Cache and object cache via Redis Object Cache - both separately and together (the latter being recommended on the Fastest Cache forums).

I have tried using the Relevanssi plugin to improve the search. I have tried adding high-performance keys (database indexes) using the Index WP MySQL For Speed plugin. I have tried cleaning up the database with the Advanced Database Cleaner plugin.

The Query monitor plugin does show several SQL queries which take several seconds to complete. When I run these queries directly from the command line, they are just as slow as when the search is performed on the front end.

Example:

SELECT COUNT(DISTINCT(relevanssi.doc))
FROM wp_relevanssi AS relevanssi
WHERE (relevanssi.term LIKE 'druha%'
OR relevanssi.term_reverse LIKE CONCAT(REVERSE('druha'), '%'))

The wp_posts table has ca. 150.000 records, wp_postmeta has ca. 20 million records. Each item has over 70 custom fields. Is there a way to speed up the search with this size of the database? Thank you very much.

本文标签： mysqlSlow search queries with a large database