如何加速这个Wordpress Mysql查询？

Question

我在Wordpress网站上建立了一个搜索功能，用于搜索姓名和地点，如下所示：

#name search
select user_id from wp_usermeta where meta_key='first_name' 
AND meta_value LIKE 'term' 
AND user_id in (SELECT post_author FROM wp_posts WHERE post_type='attachment' 
AND length(post_content) > 0 
AND length(post_title) > 0 
AND post_author in (SELECT post_author FROM wp_posts WHERE post_type='article')) 

UNION 

#locality search
select user_id from wp_usermeta where meta_key in ('zip', 'city', 'state', 'country') 
AND user_id in (select user_id from wp_usermeta where meta_key = 'user_type'and meta_value = 'user') 
AND (meta_value LIKE 'term') 
AND user_id in (SELECT post_author FROM wp_posts WHERE post_type='attachment' 
AND length(post_content) > 0 
AND length(post_title) > 0 
AND post_author in (SELECT post_author FROM wp_posts WHERE post_type='article')) 
group by user_id

这大约需要3秒的运行时间。我正在尝试看看是否有更快的方法可以编写这个查询。我有innoDB和索引，但有很多记录。这是查询的最快版本，但3秒仍然是相当慢的。有什么想法吗？

编辑（SQL explain结果，倒数第二个值是行数）：

“6”,“dependent subquery”,“wp_posts”,“index_subquery”,“type_status_date,post_author”,“post_author”,“8”,“func”,“1”,“using where”

“7”,“dependent subquery”,“wp_posts”,“index_subquery”,“type_status_date,post_author”,“post_author”,“8”,“func”,“1”,“using where”

“5”,“依赖子查询”,“wp_usermeta”,“ref”,“user_id,meta_key,fh_danb_1”,“fh_danb_1”,“776”,“const,func”,“1”,“using where”

wp_usermeta有超过500k行，但我认为我应该索引列meta_value？

不确定是否可以使查询本身在语法上更快。

这里使用的两个表是wp_posts（post_author,post_date,post_date_gmt,post_content,post_title,post_excerpt,post_status,comment_status,ping_status,post_password,post_name,to_ping,pinged,post_modified_gmt,post_content_filtered,post_parent,guid,menu_order,post_type,post_mime_type,comment_count,up,down,wp_posts.post_author值与wp_usermeta.user_id相同。Wp_posts有超过100k行，wp_usermeta有超过500k行。

Answer 1

我无法测试您的查询，但我会这样做：

• 用内部连接替换子句
• 将meta_value='term'替换为meta_value='term'
• 删除分组依据作为联合查询（不包含union ALL子句）已删除重复项
• 在键上添加索引，也可能在某些文本字段上添加索引。

如果我没有犯任何错误，这应该是结果查询：

select wp_usermeta.user_id
from
  wp_usermeta inner join wp_posts wp1
  on wp_usermeta.user_id=wp1.post_author and wp1.post_type='attachment'
  inner join wp_posts wp2
  on wp_usermeta.user_id=wp2.post_author and post_type='article'
where
  wp_usermeta.meta_key='first_name' 
  AND wp_usermeta.meta_value='term' 
  AND length(wp_usermeta.post_content) > 0 
  AND length(wp_usermeta.post_title) > 0 

UNION 

select wp_um1.user_id
from
  wp_usermeta wp_um1 inner join wp_usermeta wp_um2
  on wp_um1.user_id=wp_um2.user_id and wp_um2.meta_key = 'user_type'
     and wp_um2.meta_value = 'user'
  inner join wp_posts wp1
  on wp_usermeta.user_id=wp1.post_author and wp1.post_type='attachment'
  inner join wp_posts wp2
  on wp_usermeta.user_id=wp2.post_author and post_type='article'
where
  wp_um1.meta_key in ('zip', 'city', 'state', 'country')
  AND (wp_um1.meta_value = 'term')
  AND length(wp_um1.post_content) > 0
  AND length(wp_um1.post_title) > 0

 inner join wp_posts wp1
  on wp_usermeta.user_id=wp1.post_author and wp1.post_type='attachment'
  inner join wp_posts wp2
  on wp_usermeta.user_id=wp2.post_author and post_type='article'

inner join (select post_author
            from wp_posts
            where post_type in ('attachment','article')
            group by post_author
            having count(*)>1) wp on wp_usermeta.user_id = wp.post_author