慢sql select。两张桌子

Question

我的脚本运行太慢，当搜索邮政编码在30英里的90210。它复制了一些，即使在邮政编码数据库中也没有dups，因此是独特的u.id。除了只选择我需要的列之外，有什么关于如何加快速度的想法吗？

<?php
$zipcode = queryDB("SELECT * FROM zipcodes WHERE zipcode='$location' LIMIT 1", 'a');

$distance = 30;
$sql = "SELECT DISTINCT zipcode, ( 3959 * acos( cos( radians(".$zipcode['lat'].") ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(".$zipcode['lon'].") ) + sin( radians(".$zipcode['lat'].") ) * sin( radians( lat ) ) ) ) AS distance FROM zipcodes HAVING distance <= $distance ORDER BY distance";
$zipcodes = queryDB($sql, 'r');

$sql = '';
while($row = mysqli_fetch_assoc($zipcodes)){
    $sql .= "u.category='$category' AND u.zipcode='".$row['zipcode']."' AND u.zipcode = l.zipcode";
    if($_GET['photos'] == 'y'){ $sql .= " AND photo_1!=''"; }
    $sql .= " OR ";
}
$sql = rtrim($sql, ' OR ');

$profiles = queryDB("SELECT DISTINCT u.id, u.*, l.city, l.state, l.zipcode FROM user_accounts as u, zipcodes as l WHERE $sql ORDER BY date_added DESC", 'r');
while($row = mysqli_fetch_assoc($profiles)){
//this is where i display the profile information
} ?>

更新:最大的问题是没有在我的user_accounts select上使用联接。下面是我更新的sql查询。

    $sql = '';
$zipcodes = new ZipCodesRange($location, $distance);
foreach ($zipcodes->zipCodes as $zipcode){
    $sql .= "(u.category='$category' AND u.zipcode='".$zipcode['ZIPCODE']."'";
    if($_GET['photos'] == 'y'){ $sql .= " AND photo_1!=''"; }
    $sql .= ") OR ";
}
$sql = rtrim($sql, ') OR ');
$sql .= ")";


$profiles = queryDB("SELECT u.id, u.photo_1, u.name, u.business, u.date_added, l.city, l.state, l.zipcode FROM user_accounts as u INNER JOIN zipcodes as l ON u.zipcode = l.zipcode WHERE $sql ORDER BY date_added DESC", 'r');

Answer 1

根据这个解释半正弦公式的其他question的@malias，这里有一个文档，展示了提高这个sql：http://tr.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL速度的不同方法。

主要目标是限制要搜索的邮政编码，这样您就不必计算这么多记录的半正弦。建议添加一个WHERE子句来过滤纬度和经度的界限。表索引可用于此矩形过滤器，然后半正弦可用于将其过滤为球体。