0

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%%')
  • 2
    It's a meta query, queries that use meta, be it post meta or user meta to find things are super slow. There isn't much that can be done other than caching it, it's inherently expensive – Tom J Nowell Nov 8 '16 at 21:40
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'
  • thanks, but when i add try add query: via $result = mysql_query(" and you query, Nothing(( not working query more – user89886 Nov 9 '16 at 0:18
  • @Lerry The subselect was using id instead of user_id as column name. I corrected that now. – JHoffmann Nov 9 '16 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy