Optimize slow SQL query for multiple meta values

Question

Can you help me to optimize this slow query? The page containing this query is loading slowly:

SELECT a.id, b1.meta_value AS display_name, b2.meta_value as user_nicename, b3.meta_value as user_organization, b4.meta_value as user_tilte, b5.meta_value as user_street_address, b6.meta_value as user_city,  b7.meta_value as user_state, b8.meta_value as user_phone, b9.meta_value as page_key, b10.meta_value as page_id 
FROM wp_users a
INNER JOIN wp_usermeta b1 ON b1.user_id = a.ID AND b1.meta_key = 'display_name' 
INNER JOIN wp_usermeta b2 ON b2.user_id = a.ID AND b2.meta_key = 'user_nicename'
INNER JOIN wp_usermeta b3 ON b3.user_id = a.ID AND b3.meta_key = 'user_organization'
INNER JOIN wp_usermeta b4 ON b4.user_id = a.ID AND b4.meta_key = 'user_tilte'
INNER JOIN wp_usermeta b5 ON b5.user_id = a.ID AND b5.meta_key = 'user_street_address'
INNER JOIN wp_usermeta b6 ON b6.user_id = a.ID AND b6.meta_key = 'user_city'
INNER JOIN wp_usermeta b7 ON b7.user_id = a.ID AND b7.meta_key = 'user_state'
INNER JOIN wp_usermeta b8 ON b8.user_id = a.ID AND b8.meta_key = 'user_phone'
INNER JOIN wp_usermeta b9 ON b9.user_id = a.ID AND b9.meta_key = 'page_key'
INNER JOIN wp_usermeta b10 ON b10.user_id = a.ID AND b10.meta_key = 'page_id'
WHERE (b1.meta_value LIKE '%%%s%%' OR b2.meta_value LIKE '%%%s%%' OR b3.meta_value LIKE '%%%s%%' OR b4.meta_value LIKE '%%%s%%' OR b5.meta_value LIKE '%%%s%%' OR b6.meta_value LIKE '%%%s%%' OR b7.meta_value LIKE '%%%s%%' OR b8.meta_value LIKE '%%%s%%' OR b9.meta_value LIKE '%%%s%%' OR b10.meta_value LIKE '%%%s%%')

Answer 1

First you can optimize a little by not joining with wp_users as the joins can all be done between wp_usermeta. Another optimization would be to replace the WHERE clause with a subselect (which MySQL manages to optimize so it only gets executed once). The meta_key clause parts should be put into the WHERE clause I guess, but I don't know if that has any performance impact.

SELECT b1.user_id AS id, b1.meta_value AS display_name, b2.meta_value as user_nicename, b3.meta_value as user_organization, b4.meta_value as user_tilte, b5.meta_value as user_street_address, b6.meta_value as user_city,  b7.meta_value as user_state, b8.meta_value as user_phone, b9.meta_value as page_key, b10.meta_value as page_id 
FROM wp_usermeta b1 
INNER JOIN wp_usermeta b2 ON b1.user_id = b2.user_id
INNER JOIN wp_usermeta b3 ON b1.user_id = b3.user_id
INNER JOIN wp_usermeta b4 ON b1.user_id = b4.user_id
INNER JOIN wp_usermeta b5 ON b1.user_id = b5.user_id
INNER JOIN wp_usermeta b6 ON b1.user_id = b6.user_id
INNER JOIN wp_usermeta b7 ON b1.user_id = b7.user_id
INNER JOIN wp_usermeta b8 ON b1.user_id = b8.user_id
INNER JOIN wp_usermeta b9 ON b1.user_id = b9.user_id
INNER JOIN wp_usermeta b10 ON b1.user_id = b10.user_id
WHERE b1.user_id IN 
    (SELECT a.user_id FROM wp_usermeta AS a WHERE a.meta_value LIKE '%%%s%%')
    AND b1.meta_key = 'display_name' 
    AND b2.meta_key = 'user_nicename'
    AND b3.meta_key = 'user_organization' 
    AND b4.meta_key = 'user_tilte' 
    AND b5.meta_key = 'user_street_address' 
    AND b6.meta_key = 'user_city' 
    AND b7.meta_key = 'user_state' 
    AND b8.meta_key = 'user_phone' 
    AND b9.meta_key = 'page_key' 
    AND b10.meta_key = 'page_id'