单页Wordpress慢SQL查询

Question

我有一个工作搜索框，它使用以下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%')
       );

这个查询有时会非常快，比如在一秒内，但有时它会在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查询？

Answer 1

我建议将这段代码编写为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)上。