时间戳

首页 > 解决方案 > Wordpress 单页慢速 SQL 查询

sql - Wordpress 单页慢速 SQL 查询

问题描述

我有一个搜索自定义帖子类型的职位搜索框 -jobs使用以下 SQL 查询：

SELECT ID
FROM wp_posts AS posts INNER JOIN
     wp_postmeta AS postmeta
     ON postmeta.post_id = posts.ID
 WHERE post_type='job' AND post_status='publish' AND
       ((postmeta.meta_key = 'jobsearch_field_job_posted_by' AND
         postmeta.meta_value IN (1640,2764,11487,11783,24831,24985,33113,39346,40194,41158,43157,44307,45447)
        ) OR
        (posts.post_title LIKE '%Con%')
       );

此查询有时会在 1 秒内运行得非常快，但有时它会在 3 秒内给出结果。

这是解释计划：

+------+-------------+----------+------+--------------------------+------------------+---------+---------------------+------+-----------------------+
| id   | select_type | table    | type | possible_keys            | key              | key_len | ref                 | rows | Extra                 |
+------+-------------+----------+------+--------------------------+------------------+---------+---------------------+------+-----------------------+
|    1 | SIMPLE      | posts    | ref  | PRIMARY,type_status_date | type_status_date | 164     | const,const         | 2897 | Using index condition |
|    1 | SIMPLE      | postmeta | ref  | post_id,meta_key         | post_id          | 8       | afkmgfwugp.posts.ID | 20   | Using where           |
+------+-------------+----------+------+--------------------------+------------------+---------+---------------------+------+-----------------------+

如何优化此 SQL 查询？

标签： sqlwordpress

解决方案

我建议把它写成union all：

SELECT p.ID
FROM wp_posts p
WHERE p.post_type = 'job' AND p.post_status = 'publish' AND
      p.post_title LIKE '%Con%'
UNION ALL
SELECT p.ID
FROM wp_posts p INNER JOIN
     wp_postmeta pm
     ON pm.post_id = p.ID
WHERE p.post_type = 'job' AND p.post_status = 'publish' AND
      p.post_title NOT LIKE '%Con%' AND
      pm.meta_key = 'jobsearch_field_job_posted_by' AND
      pm.meta_value IN (1640, 2764, 11487, 11783, 24831, 24985, 33113, 39346, 40194, 41158, 43157, 44307, 45447);

我会推荐wp_posts(post_type, post_status, post_title). 和上wp_postmeta(meta_key, meta_value, post_id)。

推荐阅读