如何有效地找到给定位置附近的最近位置?mysql

我正在编写一个脚本，其中将业务量以纬度和经度加载到mySQL数据库中。然后，我向该脚本提供一个（最终用户的）经度纬度，并且该脚本必须计算从提供的经度/经度到它从数据库中获得的条目的EACH的距离，并按从最远到最远的顺序对其进行排序。

实际上，我实际上只需要大约10或20个“最近”的结果，但是除了从数据库中获取所有结果并对每个结果运行函数然后进行数组排序之外，我什么也想不做。

这是我已经拥有的：

$lat,"long"=>$long); $sql = "SELECT * FROM mellow_listings WHERE active=1"; $result = mysql_query($sql); while($row = mysql_fetch_array($result)){ $thedistance = getDistance($startPoint,array("lat"=>$row['lat'],"long"=>$row['long'])); $data[] = array('id' => $row['id'], 'name' => $row['name'], 'description' => $row['description'], 'lat' => $row['lat'], 'long' => $row['long'], 'address1' => $row['address1'], 'address2' => $row['address2'], 'county' => $row['county'], 'postcode' => strtoupper($row['postcode']), 'phone' => $row['phone'], 'email' => $row['email'], 'web' => $row['web'], 'distance' => $thedistance); } // integrate google local search $url = "http://ajax.googleapis.com/ajax/services/search/local?"; $url .= "q=Off+licence"; // query $url .= "&v=1.0"; // version number $url .= "&rsz=8"; // number of results $url .= "&key=ABQIAAAAtG" ."Pcon1WB3b0oiqER" ."FZ-TRQgsWYVg721Z" ."IDPMPlc4-CwM9Xt" ."FBSTZxHDVqCffQ2" ."W6Lr4bm1_zXeYoQ"; // api key $url .= "&sll=".$lat.",".$long; // sendRequest // note how referer is set manually $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_REFERER, /* url */); $body = curl_exec($ch); curl_close($ch); // now, process the JSON string $json = json_decode($body, true); foreach($json['responseData']['results'] as $array){ $thedistance = getDistance($startPoint,array("lat"=>$array['lat'],"long"=>$array['lng'])); $data[] = array('id' => '999', 'name' => $array['title'], 'description' => '', 'lat' => $array['lat'], 'long' => $array['lng'], 'address1' => $array['streetAddress'], 'address2' => $array['city'], 'county' => $array['region'], 'postcode' => '', 'phone' => $array['phoneNumbers'][0], 'email' => '', 'web' => $array['url'], 'distance' => $thedistance); } // sort the array foreach ($data as $key => $row) { $id[$key] = $row['id']; $distance[$key] = $row['distance']; } array_multisort($distance, SORT_ASC, $data); header("Content-type: text/xml"); echo ' '."\n"; echo ''."\n"; echo ' '."\n"; echo ' '."\n"; for($i = 0; isset($distance[$i]); $i++){ //echo $data[$i]['id']." -> ".$distance[$i]."
"; echo ' '."\n"; foreach($data[$i] as $key => $val){ echo ' '."\n"; echo ' '."\n"; } echo ' '."\n"; } echo ' '."\n"; echo ' '."\n"; ?> 现在，它在数据库中只有2或3个业务的情况下运行得足够快，但是我目前正在将5k个业务加载到数据库中，并且我担心它在每次输入时都会非常慢地运行吗？你怎么看？

它也不是我可以缓存的数据类型，因为两个用户具有相同的经/纬度的可能性极少出现，因此无济于事。

我该怎么办？

感谢您的帮助和建议。他们都很感谢。