CODEWITHSUNDEEP
mysqlwordpressentity-attribute-value
P Heron
P Heron

Reputation: 13

Optimizing/ MySQL query

I am fairly new to MySQL and have been working on the following query to pull a list of values from the wordpress users/usermeta tables. The query works apart from the performance is terrible and im not sure how to improve it.

The query takes 12+ seconds to complete and with only 400 or so users this is going to become a huge problem if more are added.

I have noticed that MySQL reports that there are no unique columns but im not sure how to fix this as the user ID is part of the results already.

This is the default query, the full query adds in an extra calculation using the lat/long values to determine distance for a postcode search, this however does not seem to add much additional time.

The main part I struggled with is the fact that the values are saved as meta_key and meta_value with the associated user_id as a foreign key. 

SELECT DISTINCT 
  wp_users.ID,
  wp_users.user_email,
  city_latitude.meta_value as cityLat,
  city_longitude.meta_value as cityLong,
  service_name.meta_value as service_name,
  service_address.meta_value as service_address,
  service_category.meta_value as service_category,
  service_level.meta_value as service_level,
  service_info.meta_value as service_info,
  service_area.meta_value as service_area,
  service_active.meta_value as service_active,
  service_keyword.meta_value as service_keyword
FROM 
  wp_usermeta AS city_latitude
  LEFT JOIN wp_usermeta as city_longitude ON city_latitude.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_name ON service_name.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_category ON service_category.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_address ON service_address.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_level ON service_level.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_info ON service_info.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_area ON service_area.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_active ON service_active.user_id = city_longitude.user_id
  LEFT JOIN wp_usermeta as service_keyword ON service_keyword.user_id = city_longitude.user_id
  INNER JOIN wp_users ON wp_users.ID = city_latitude.user_id

WHERE city_latitude.meta_key = 'service_lat' 
  AND city_longitude.meta_key = 'service_long' 
  AND (service_name.meta_key = 'service_name' AND service_name.meta_value != 'Anonymous') 
  AND (service_category.meta_key = 'service_category' ".$query_category.") 
  AND service_address.meta_key = 'service_address' 
  AND (service_level.meta_key = 'wp_user_level' AND service_level.meta_value = 0) 
  AND service_info.meta_key = 'service_additional' 
  AND service_area.meta_key = 'service_area' 
  AND service_keyword.meta_key = 'service_keywords'
  AND (service_active.meta_key = 'active' AND service_active.meta_value = 1)  
ORDER BY service_name ASC

I have updated the query with some of the suggestions below which has already made a big difference. I still think the query itself could be improved by reducing the number of joins etc, if anyone has any more ideas I would love to hear them.

This is the query which includes the changes and the additional part for searching by distance.

SELECT DISTINCT 
  wp_users.ID,
  wp_users.user_email,
  city_latitude.meta_value as cityLat,
  city_longitude.meta_value as cityLong,
  service_name.meta_value as service_name,
  service_address.meta_value as service_address,
  service_category.meta_value as service_category,
  service_level.meta_value as service_level,
  service_info.meta_value as service_info,
  service_area.meta_value as service_area,
  service_active.meta_value as service_active,
  service_keyword.meta_value as service_keyword,
  ((ACOS(SIN($userLat * PI() / 180) * SIN(city_latitude.meta_value * PI() / 180) + COS($userLat * PI() / 180) * COS(city_latitude.meta_value * PI() / 180) * COS(($userLng - city_longitude.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
FROM 
  wp_usermeta AS usermeta
  LEFT JOIN wp_usermeta as city_longitude ON city_longitude.user_id = usermeta.user_id AND city_longitude.meta_key = 'service_long'
  LEFT JOIN wp_usermeta as city_latitude ON city_latitude.user_id = usermeta.user_id AND city_latitude.meta_key = 'service_lat'
  LEFT JOIN wp_usermeta as service_name ON service_name.user_id = usermeta.user_id AND service_name.meta_key = 'service_name'
  LEFT JOIN wp_usermeta as service_category ON service_category.user_id = usermeta.user_id AND service_category.meta_key = 'service_category'
  LEFT JOIN wp_usermeta as service_address ON service_address.user_id = usermeta.user_id AND service_address.meta_key = 'service_address'
  LEFT JOIN wp_usermeta as service_level ON service_level.user_id = usermeta.user_id AND service_level.meta_key = 'wp_user_level'
  LEFT JOIN wp_usermeta as service_info ON service_info.user_id = usermeta.user_id AND service_info.meta_key = 'service_additional'
  LEFT JOIN wp_usermeta as service_area ON service_area.user_id = usermeta.user_id AND service_area.meta_key = 'service_area'
  LEFT JOIN wp_usermeta as service_active ON service_active.user_id = usermeta.user_id AND service_active.meta_key = 'active'
  LEFT JOIN wp_usermeta as service_keyword ON service_keyword.user_id = usermeta.user_id AND service_keyword.meta_key = 'service_keywords'
  INNER JOIN wp_users ON wp_users.ID = usermeta.user_id
WHERE (service_name.meta_value != '' AND service_name.meta_value != 'Anonymous') AND service_level.meta_value = 0 AND service_active.meta_value = 1 
HAVING distance < $search_distance
ORDER BY distance ASC

Upvotes: 0

Views: 69

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can try this. if you send me more data i can more optimize the query for speed. You must modify the calculation from distance. i have replace the PHP values with a const (2) for testing and also remove the remark at the having line

SELECT
    res.*
    ,((ACOS(SIN(2 * PI() / 180) * SIN(cityLat * PI() / 180) 
     + COS(2 * PI() / 180) * COS(cityLat * PI() / 180)
     * COS((2 - cityLong) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
    FROM (
        SELECT  
            wpu.ID
            , wpu.user_email
            , GROUP_CONCAT(if(wpm.meta_key = 'service_lat'          ,wpm.meta_value,'') SEPARATOR '') AS cityLat
            , GROUP_CONCAT(if(wpm.meta_key = 'service_long'         ,wpm.meta_value,'') SEPARATOR '') AS cityLong
            , GROUP_CONCAT(if(wpm.meta_key = 'service_name'         ,wpm.meta_value,'') SEPARATOR '') AS service_name
            , GROUP_CONCAT(if(wpm.meta_key = 'service_address'      ,wpm.meta_value,'') SEPARATOR '') AS service_address
            , GROUP_CONCAT(if(wpm.meta_key = 'service_category'     ,wpm.meta_value,'') SEPARATOR '') AS service_category
            , GROUP_CONCAT(if(wpm.meta_key = 'wp_user_level'            ,wpm.meta_value,'') SEPARATOR '') AS service_level
            , GROUP_CONCAT(if(wpm.meta_key = 'service_additional'   ,wpm.meta_value,'') SEPARATOR '') AS service_info
            , GROUP_CONCAT(if(wpm.meta_key = 'service_area'         ,wpm.meta_value,'') SEPARATOR '') AS service_area   
            , GROUP_CONCAT(if(wpm.meta_key = 'active'                   ,wpm.meta_value,'') SEPARATOR '') AS service_active
            , GROUP_CONCAT(if(wpm.meta_key = 'service_keywords'     ,wpm.meta_value,'') SEPARATOR '') AS service_keyword
    FROM wp_users AS wpu
    LEFT JOIN wp_usermeta as wpm ON wpm.user_id = wpu.id    
    GROUP BY wpm.user_id
) as res
WHERE
    (res.service_name != '' AND res.service_name != 'Anonymous')
    AND res.service_level = 0
    AND res.service_active = 1 
-- HAVING distance < $search_distance
ORDER BY distance ASC;

result

ID  user_email  meta_key    cityLat cityLong    service_name    service_address service_category    service_level   service_info    service_area    service_active  service_keyword distance
2   [email protected]  wp_user_level   54.90131    -1.385126   service2    7 address1 address2 Support Info and Advice|Health and Wellbeing|Things to do|Education Training|Volunteering   0   A strong, local, independent charity working with and for older people (those 50+)  Sunderland  1       3659.9253142487732
3   [email protected]  wp_user_level   54.897923   -1.514989   service 3   6 address1 address2 Support Info and Advice|Children Young people and Families|Health and Wellbeing|Things to do|Housing and your home|Education Training|Employment|Volunteering|Money Matters|Mental Health|Cultural  0   is a local independent charity offering a range of mental health and wellbeing services and training for the local community throughout .   Sunderland  1       3660.080614342941

Upvotes: 0

Seb
Seb

Reputation: 1551

You need to add the where conditions directly in the ON parts. Otherwise you will first join anything (which is huge) and then reduce it. So you can reduce it directly when joining. Shoud be much faster.

LEFT JOIN wp_usermeta as city_longitude ON city_latitude.user_id = city_longitude.user_id AND city_longitude.meta_key = 'service_long'

and so on

Upvotes: 1

Geek
Geek

Reputation: 653

I think your query is quite huge. In wordpress I am not sure if you can break it into smaller parts and then combine its results.

If you want to keep this query I suggest 1) you check that the necessary columns on which you are doing join have index on them 2) necessary columns that are in where clause are indexed.

Necessary columns : You can actually put index on all the columns in join and where but it will decrease the insertion speed . so you can try putting index on the column which belong to table which are huge.

Upvotes: 0

Related Questions