mysql 使用内部连接优化查询 where 和 order by 和 co-ordinates

Question

我只是想掌握分析。

我之前有一个简单的查询优化，但这个查询涉及更多，所以我不确定在这种情况下我可能需要向哪些列添加索引!

SELECT ReportID, Title, Description, postPic , DatePosted, UserID, FName, SName, authorNotificationId, userPic,  Lat, `Long`, 
photoWidth, photoHeight 
FROM 
(SELECT
        (((acos(sin((?*pi()/180)) 
        * sin((`Lat`*pi()/180))+cos((?*pi()/180))
        * cos((`Lat`*pi()/180)) * cos(((?- `Long`)
        *pi()/180))))*180/pi())*60*1.1515*1609.34)
        AS distance, Title, Description, posts.Pic as postPic, ReportID, DatePosted, posts.UserID as UserID, FName, SName, NotificationID as authorNotificationId, users.Pic as userPic,
        Lat, `Long`, photoWidth, photoHeight
        FROM posts
        INNER JOIN Users 
        ON Users.UserID = posts.UserID) foo
        WHERE distance <=? AND (Title LIKE ? OR Description LIKE ? OR FName LIKE ? OR SName LIKE ?) 
ORDER BY DatePosted DESC LIMIT ?, 10

查询简要说明:它尝试检索用户预定义半径内的所有帖子 - 通过检查用户注册位置的坐标与帖子的关联坐标之间的距离(并检查该距离是否小于用户注册半径)

运行 explain 给我以下信息:

id             : 1
select_type    : Primary
table          : <derived2>
type           : **ALL**
possible_keys  : NULL
key            : NULL
key_len        : NULL
ref            : NULL
rows           : 824
Extra          : Using where; **Using filesort**

id             : 2
select_type    : DERIVED
table          : tableposts
type           : ALL
possible_keys  : NULL
key            : NULL
key_len        : NULL
ref            : NULL
rows           : 824
Extra          : NULL

id             : 2
select_type    : DERIVED
table          : Users
type           : eq_ref
possible_keys  : PRIMARY
key            : PRIMARY
key_len        : 4
ref            : db.posts.UserID
rows           : 1
Extra          : NULL

由于 ALL select_type 和 extra 中的 filesort，我是否只需要担心查询 1 和 2。

我是否必须为列标题和描述添加索引？我已经在用户表中有一个关于 fname 和 sname 的索引。

正如 Olli 所指出的，距离是计算的结果，因此我无法为其添加索引

更新

从 Olli 的回答中，我从 EXPLAIN 中得到以下信息:

id           : 1
select_type  : SIMPLE
table        : posts
type         : ALL
possible_keys: postsUserID_idx
key          : NULL
key_len      : NULL
ref          : NULL
rows         : 825
Extra        : Using where; Using filesort

id           : 1
select_type  : SIMPLE
table        : Users
type         : eq_ref
possible_keys: PRIMARY
key          : PRIMARY
key_len      : 4
ref          : db.posts.UserID
rows         : 1
Extra        : Using where

更新 2:

根据 symcbean 的建议，表重组的初始尝试是添加另一个 point 类型的列:

set @lat= 53.277656872106;
set @lon = -9.01179972749435;
set @dist = CONV_MI_KM(50000, 'k')/1000;
set @rlon1 = @lon-@dist/abs(cos(radians(@lat))*69);
set @rlon2 = @lon+@dist/abs(cos(radians(@lat))*69);
set @rlat1 = @lat-(@dist/69);
set @rlat2 = @lat+(@dist/69);

//set @geoCol = astext(envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2))));
set @geoCol = astext(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2)));
set @geoCol2 = linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2));

我需要更改我的注册准备语句:

INSERT INTO users VALUES 
    ('', ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, 0, ?, 0)

在表末尾 0 之后包含 newCol。这应该是我定义的 geoCol2 吗？不确定的原因是在 php 中绑定(bind)参数时我想我可能需要使用“s”作为绑定(bind)类型，因为在查找可以绑定(bind)到语句的参数类型时，我只看到 s，i， d 和 b 作为选项。还是我离题了？

Answer 1

您是以英里还是公里计算距离？

你能试试这个查询吗(应该是一样的，只是没有子查询):

SELECT 
  (((acos(sin((?*pi()/180)) 
    * sin((`Lat`*pi()/180))+cos((?*pi()/180))
    * cos((`Lat`*pi()/180)) * cos(((?- `Long`)
    *pi()/180))))*180/pi())*111189.3006) AS distance,
  ReportID, 
  Title, 
  Description, 
  postPic , 
  DatePosted, 
  UserID, 
  FName, 
  SName, 
  authorNotificationId, 
  userPic,  
  Lat, 
  `Long`, 
  photoWidth, 
  photoHeight 
FROM
  posts 
JOIN Users ON (Users.UserID = posts.UserID) 
WHERE (Title LIKE ? OR Description LIKE ? OR FName LIKE ? OR SName LIKE ?) 
HAVING distance <= ?
ORDER BY 
  DatePosted DESC LIMIT ?, 10

由于您是动态计算距离的，因此您的检查需要 HAVING 子句，否则使用 WHERE 时它会因未知列而失败。

您或许也可以为这个添加执行计划？