mysql优化内部连接的查询，其中和顺序和坐标

I'm just trying to get to grips with profiling.

我只是试图掌握剖析。

I had a simply query optimised earlier but this one has a bit more involved so I'm not sure which columns I may need to add an index to in this case!

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

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

Brief explanation of query: it tries to retrieve all posts within a user's pre-defined radius - by checking the distance between the coordinates of the user's registered location and the associated coordinates of a post (and checking that that distance is less than the user's registered radius)

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

Running explain gives me the following:

运行说明给我以下内容：

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

Is it the case that I only need to worry about query 1 and 2 due to the ALL select_type and the filesort in the extra.

是不是因为ALL select_type和extra中的filesort而需要担心查询1和2。

Would I have to add an index to the column title and description? I already have an index on fname and sname in the users table.

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

As Olli pointed out, distance is a result of a calculation so I can't add an index to that

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

UPDATE

UPDATE

From Olli's answer, I get the following from EXPLAIN:

从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

Update 2:

更新2：

Initial attempt for table restructure to add another col of type point as per symcbean's suggestion:

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

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

I need to change my register prepared statement from:

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

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

to include the newCol at the end of the table after 0. Should this be what I've defined as geoCol2? The reason for the uncertainty is when binding the param in php I think I may need to use "s" as the bind type, as when looking up the types of params that can be bound to a statement, I only see s, i, d and b as options. Or am I off the mark?

在0之后的表格末尾包含newCol。这应该是我所定义的geoCol2吗？不确定的原因是在php中绑定param我认为我可能需要使用“s”作为绑定类型，就像查找可以绑定到语句的params类型一样，我只看到s，i， d和b作为选项。还是我没有了？

1 个解决方案

#1

Are you calculating the distance in miles or kilometers?

你在计算以英里或公里为单位的距离吗？

Could you please try this query (should be the same, only without subquery):

你可以试试这个查询（应该是相同的，只有没有子查询）：

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

Since you calculate the distance on the fly, you need the HAVING clause for your check, otherwise with WHERE it would fail with unknown column.

由于您在运行中计算距离，因此您需要使用HAVING子句进行检查，否则对于WHERE，它将因未知列而失败。

You can probably add the execution plan for this one as well?

你也可以为这个添加执行计划吗？

#1