How to speed up this Wordpress Mysql query?

Question

I have a search function I built on a Wordpress site for searching names and localities that is like so:

#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

This takes about 3 seconds to run. I am trying to see if there is a faster way I can write this query. I have innoDB and indexing but there are a lot of records. This is the fastest version of the query I have but 3 seconds is still pretty slow. Any ideas?

EDIT (SQL explain results, the second to last value is the number of rows):

'1', 'PRIMARY', 'wp_usermeta', 'ref', 'meta_key,FH_DanB_1', 'FH_DanB_1', '768', 'const', '98252', 'Using where'

'2', 'DEPENDENT SUBQUERY', 'wp_posts', 'index_subquery', 'type_status_date,post_author', 'post_author', '8', 'func', '1', 'Using where'

'3', 'DEPENDENT SUBQUERY', 'wp_posts', 'index_subquery', 'type_status_date,post_author', 'post_author', '8', 'func', '1', 'Using where'

'4', 'UNION', 'wp_usermeta', 'index', 'meta_key,FH_DanB_1', 'user_id', '8', NULL, '510714', 'Using where'

'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', 'DEPENDENT SUBQUERY', 'wp_usermeta', 'ref', 'user_id,meta_key,FH_DanB_1', 'FH_DanB_1', '776', 'const,func', '1', 'Using where'

wp_usermeta has over 500k rows, but I think I should index the column meta_value?

Not sure if I can make the query itself syntactically faster.

Two tables used here are 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, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count, up, down, popularity, robotsmeta, meta_robots) and wp_usermeta (meta_id, user_id, meta_key, meta_value). The wp_posts.post_author value is the same as wp_usermeta.user_id. Wp_posts has over 100K rows, wp_usermeta has over 500k.

Please post the table structure and an EXPLAIN of the query. — G-Nugget
Can it be cached in any way perhaps? At least partially. Or perhaps you could make a table that's is a copy of what you need from the other tables. Selecting from such a table is probably way faster. I know WP has some DB storage hooks you might use to be able to keep the copied table in sync with the original tables. — Andreas Hagen

fthiella fthiella · Accepted Answer · 2012-12-13T20:46:01

I can't test your query, but i would do this:

substitute IN clauses with inner joins
substitute meta_value LIKE 'term' with meta_value='term'
remove GROUP BY as union queries (without union all clause) already removes duplicates
add indexes on keys, and maybe also on some text fields.

If I have not made any mistake, this should be the resulting query:

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

EDIT: the last think that i would like to try is this. You could substitute this part:

 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'

whith this:

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

depending on how is your data structured, it could make the query faster, but also slower...

How to speed up this Wordpress Mysql query?

1 Answers