MYSQL -左联接，优化2秒查询

Question

我试图优化我的MySQL查询，我已经将所有表合并到一个似乎有用的表中。

但是查询仍然需要超过2秒..。有办法让它更快吗？

在user_id上总是一个索引。MySQL 5.5.12，除城市外，所有表都是InnoDB表。

SELECT b.user_id,b.firstname,b.lastname,b.address,b.zipcode,b.city
  , ($calculatings) AS Distance
FROM `user_profiles` `b`
LEFT JOIN `cities` `a` ON `a`.`postal`=`b`.`zipcode` 
JOIN `users` `u` ON `b`.`user_id`=`u`.`id` 
JOIN `user_consultants` `c` ON `b`.`user_id`=`c`.`user_id` 
WHERE ($calculatings) <= 25 
      AND c.incorporated='1' 
      AND u.typ='1' 
      AND u.activated='1' 
      AND u.banned='0' 
ORDER BY Distance ASC, c.zsum_score DESC 
LIMIT 30

在var $calculatings中是距离计算的数学运算(已经优化了)。

$calculatings示例：

6368 * SQRT(2*(1-cos(RADIANS(`a`.`lat`)) * cos(0.840105508801) * 
(sin(RADIANS(`a`.`lon`)) * sin(0.201952047748) + cos(RADIANS(`a`.`lon`)) * 
cos(0.201952047748)) - sin(RADIANS(`a`.`lat`)) * sin(0.840105508801)))

，为什么这么多人离开了Joins?

1. user_profiles是用户信息的detail_table。
2. 城市是我们国家所有城市的城市餐桌，有拉特伦和更多的信息
3. 用户是用户名和密码、散列、登录尝试、禁令等的主要用户表。
4. user_consultants是一组特殊用户的附加表。

尺寸

1. user_profiles 112.000行
2. 城市68.000行
3. 用户246.000行
4. user_consultants 98.000行

解释SQL

(右击以获得全尺寸)

Answer 1

我没有时间写完整的细节，而是优化空间搜索，下面是一个简单的测试：

将(latitude, longitude)对存储在(MyISAM)表中，作为空间字段：POINT ( GEOMETRY类型的变体)。

在此字段上添加空间索引。

在查询中使用MBRContains()或MBRWithin()函数，这样就可以使用空间索引缩小在包含半径25的圆圈的正方形中的搜索范围：

WHERE MBRWithin( cities.myPointField
               , Polygon( @lat-25 @long-25
                        , @lat+25 @long-25
                        , @lat+25 @long+25
                        , @lat-25 @long+25
                        ) 
               )
  AND (yourDistanceCalculation) < 25

您可以检查MySQL文档：空间扩展

Answer 2

你应该(至少)索引: cities.postal，users.typ，users.activated，users.banned，user_consultants.incorporated

Answer 3

SELECT 
    b.user_id,b.firstname,b.lastname,b.address,b.zipcode,b.city,
    ($calculatings) AS Distance
FROM 
    `user_profiles` `b`
    JOIN `users` `u` ON `b`.`user_id`=`u`.`id`
     AND `u`.`typ`=1
     AND `u`.`activated`=1
     AND `u`.`banned`=0
    LEFT JOIN `cities` `a` ON `b`.`zipcode`=`a`.`postal` 
    LEFT JOIN `user_consultants` `c` ON `b`.`user_id`=`c`.`user_id` 
WHERE Distance <= 25
ORDER BY Distance ASC, c.zsum_score DESC
LIMIT 0,30

..。虽然，如果$calculatings的值总是相同的，而且正如我所看到的，它只依赖于表cities中的数据，那么您应该在其中添加另一列，包含预计算的距离值。

一些关于我所做的改变的笔记：

• 我只是假设typ、activated和banned是int类型的(根据查询中的值猜测)--您不应该将它们放在引号中。
• 我还假设，由于users是您的主要用户表，来自user_profiles的每个user_id都应该在users中有一个现有的id，因此您不需要LEFT。
• JOIN的速度比WHERE子句快(而WHERE比HAVING快，因为我看到了另一个使用它的答案)。
• 正如所回答的--您应该负责索引所有用作join引用的列。