位置搜索速度慢 [英] Slow location search

问题描述

我的搜索功能有问题，它可以正常工作，但加载时间在14到18秒之间，这给我带来了极大的痛苦.

I have an issue with a search function, it works correctly but is taking between 14-18 second to load and that is causing me great pain.

这是我正在使用的搜索功能:

This is the search function I am using:

$sql="select *, 
( 3959 * acos( cos( radians( $lat ) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( `lat` ) ) ) )  AS distance 
  FROM postcodes,
       details 
 WHERE `postcode`=postcodes.outcode 
 HAVING " . implode(" AND ", $searches)." 
    AND isactive='y' 
 ORDER BY `distance`";

如果有人有任何建议，他们都会受到欢迎

If anyone has any suggestions they would all be welcomed

我花了更多时间在它上面，并且在添加任何更多信息之前确切地了解了查询在做什么.

I have spent more time working on it, and understand exactly what the query is doing before I added any more information.

现在这是我的更新查询，我尝试使用左联接，但无法将邮政编码表联接到cardetails表上，当我将其反向时，cardetails列的所有字段都显示为空.

This is now my updated query, I tried using a left join but I couldn't join the postcode table onto the cardetails table, and when I reversed it the cardetails columns where all showing up null.

SELECT "column names",  
( 3959 * acos( cos( radians( $lat ) ) * cos( radians( `lat` ) ) * cos( radians(
`lng`   ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( `lat` ) ) )) 
AS       distance
FROM details
inner JOIN car_postcodes ON details.postcode =  postcodes.outcode AND
distance  < 100 AND payment='y' AND expired='n' ORDER BY     
details.price

那已经大大减少了执行查询所花费的时间(减少到大约6秒)，所以我仍然需要改进它.

That has drastically reduced the time taken to perform the query (down to around 6seconds) so I still need to improve that.

我现在遇到的问题是我无法使用列别名距离"来度量用户与表中字段之间的距离.由于我无法在where子句中使用列别名.

The problem I am having now is that I am not able to use the column alias 'distance' to measure the distance between the user and the field within the table. As I cant use column alias to within the where clause.

我在许多其他帖子上似乎都读过答案，这是一个子选择

The answer I seem to have read on many other posts is a sub select,

推荐答案

我不确定您在做什么，但以下几行可疑

I'm not sure what you are doing but the following line is suspicious

FROM postcodes,details

如果我没记错的话，这是一个 crossjoin (如果您的表很大，那只会乘以尺寸并最终得到...，我不确定您是什么意思)重新尝试在这里做

If I'm not mistaken, this is a crossjoin (if your tables are large this will just multiply the dimensions and end up with..., I'm just not sure what you're trying to do here)

这篇关于位置搜索速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！