mysql - 优化/MySQL 查询

Question

我对 MySQL 相当陌生，一直在研究以下查询以从 wordpress users/usermeta 表中提取值列表。除了性能很糟糕之外，查询还可以工作，我不知道如何改进它。

该查询需要 12 秒以上才能完成，而且只有 400 名左右的用户，如果添加更多用户，这将成为一个大问题。

我注意到 MySQL 报告没有唯一列，但我不确定如何解决这个问题，因为用户 ID 已经是结果的一部分。

这是默认查询，完整查询使用纬度/经度值添加额外的计算来确定邮政编码搜索的距离，但这似乎并没有增加太多额外的时间。

我遇到的主要问题是这些值被保存为meta_key和meta_value，并将关联的user_id作为外键。

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

我已经用下面的一些建议更新了查询，这些建议已经产生了很大的影响。我仍然认为查询本身可以通过减少连接数量等来改进，如果有人有更多的想法，我很乐意听到他们。

这是包含更改和按距离搜索的附加部分的查询。

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

Answer 1

您需要直接在ON部分添加where条件。否则你将首先加入任何东西(这是巨大的)，然后减少它。所以加入的时候可以直接减少。应该快得多。

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

等等