Wordpress查询所花费的时间超过20秒，并停留在创建索引上。

Question

嗨，我需要帮助，下面的查询，这个查询需要大约20秒，当一个以上的执行，然后服务器CPU使用率达到100%，服务器停止响应。

下面是进程列表中的查询。

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%اهمية%') OR (wp_posts.post_excerpt LIKE '%اهمية%') OR (wp_posts.post_content LIKE '%اهمية%')) AND ((wp_posts.post_title LIKE '%التصنيع%') OR (wp_posts.post_excerpt LIKE '%التصنيع%') OR (wp_posts.post_content LIKE '%التصنيع%')) AND ((wp_posts.post_title LIKE '%الغذائي%') OR (wp_posts.post_excerpt LIKE '%الغذائي%') OR (wp_posts.post_content LIKE '%الغذائي%'))) AND (wp_posts.post_password = '') AND wp_posts.post_type IN ('post', 'attachment') AND (wp_posts.post_status = 'publish') ORDER BY (CASE WHEN wp_posts.post_title LIKE '%اهمية التصنيع الغذائي %' THEN 1 WHEN wp_posts.post_title LIKE '%اهمية%' AND wp_posts.post_title LIKE '%التصنيع%' AND wp_posts.post_title LIKE '%الغذائي%' THEN 2 WHEN wp_posts.post_title LIKE '%اهمية%' OR wp_posts.post_title LIKE '%التصنيع%' OR wp_posts.post_title LIKE '%الغذائي%' THEN 3 WHEN wp_posts.post_excerpt LIKE '%اهمية التصنيع الغذائي %' THEN 4 WHEN wp_posts.post_content LIKE '%اهمية التصنيع الغذائي %' THEN 5 ELSE 6 END), wp_posts.post_date DESC LIMIT 0, 10

以下是wp_posts表的索引

+----------+------------+---------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                              | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY                               |            1 | ID            | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name                             |            1 | post_name     | A         |      172779 |      191 | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            1 | post_type     | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            2 | post_status   | A         |          42 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            3 | post_date     | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                      |            4 | ID            | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent                           |            1 | post_parent   | A         |       86389 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author                           |            1 | post_author   | A         |         226 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | posttype_new                          |            1 | post_type     | A         |          32 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | posttype_new                          |            2 | ID            | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | posttype_new                          |            3 | post_status   | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_Type_status_date                   |            1 | ID            | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_Type_status_date                   |            2 | post_type     | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_Type_status_date                   |            3 | post_status   | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            1 | post_status   | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            2 | post_password | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            3 | post_type     | A         |          40 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps          |            4 | post_modified | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            1 | post_status   | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            2 | post_password | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            3 | post_type     | A         |          42 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wpi_scalability_pro_sitemaps_postdate |            4 | post_date     | A         |      172779 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+---------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

查看您的查询，我看到了很多类似于wp_posts.post_content LIKE '%اهمية%'的东西。

您应该知道column LIKE '%matchstring%'是一个臭名昭著的MySQL性能杀手。如果您可以将您的操作更改为使用column LIKE 'matchstring%' (没有领先的%)，这将变得更好。

或者，也许对你来说，采用像Relevanssi这样的搜索插件是有意义的。(我不知道它是否适用于阿拉伯语，但在罗马字母语言中效果很好。)