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
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