执行时间——如何加快MySQL查询的速度，它能找到离给定经纬度最近的位置

Question

我的数据库中有一个邮政编码表，它与业务表一起使用，用于查找与指定邮政编码最接近的符合某些条件的企业。我要做的第一件事是获取经纬度因为它在页面上的几个地方使用。我使用:

$zipResult = mysql_fetch_array(mysql_query("SELECT latitude,longitude FROM zipCodes WHERE zipCode='".mysql_real_escape_string($_SESSION['zip'])."' Limit 1"));
$latitude = $zipResult['latitude'];
$longitude = $zipResult['longitude'];
$radius = 100;
$lon1 = $longitude - $radius / abs(cos(deg2rad($latitude))*69);
$lon2 = $longitude + $radius / abs(cos(deg2rad($latitude))*69);
$lat1 = $latitude - ($radius/69);
$lat2 = $latitude + ($radius/69);

从那里，我生成查询:

$query2 = "Select * From (SELECT business.*,zipCodes.longitude,zipCodes.latitude,
            (3956 * 2 * ASIN ( SQRT (POWER(SIN((zipCodes.latitude - $latitude)*pi()/180 / 2),2) + COS(zipCodes.latitude* pi()/180) * COS($latitude *pi()/180) * POWER(SIN((zipCodes.longitude - $longitude) *pi()/180 / 2), 2) ) )) as distance FROM business INNER JOIN zipCodes ON (business.listZip = zipCodes.zipCode)
            Where business.active = 1
            And (3958*3.1415926*sqrt((zipCodes.latitude-$latitude)*(zipCodes.latitude-$latitude) + cos(zipCodes.latitude/57.29578)*cos($latitude/57.29578)*(zipCodes.longitude-$longitude)*(zipCodes.longitude-$longitude))/180) <= '$radius'
            And zipCodes.longitude between $lon1 and $lon2 and zipCodes.latitude between $lat1 and $lat2
            GROUP BY business.id ORDER BY distance) As temp Group By category_id ORDER BY distance LIMIT 18";

结果是:

Select * 
From (SELECT business.*,zipCodes.longitude,zipCodes.latitude, (3956 * 2 * ASIN ( SQRT (POWER(SIN((zipCodes.latitude - 39.056784)*pi()/180 / 2),2) + COS(zipCodes.latitude* pi()/180) * COS(39.056784 *pi()/180) * POWER(SIN((zipCodes.longitude - -84.343573) *pi()/180 / 2), 2) ) )) as distance 
               FROM business 
               INNER JOIN zipCodes ON (business.listZip = zipCodes.zipCode) 
               Where business.active = 1 
               And (3958*3.1415926*sqrt((zipCodes.latitude-39.056784)*(zipCodes.latitude-39.056784) + cos(zipCodes.latitude/57.29578)*cos(39.056784/57.29578)*(zipCodes.longitude--84.343573)*(zipCodes.longitude--84.343573))/180) <= '100' 
               And zipCodes.longitude between -86.2099407074 and -82.4772052926 
               and zipCodes.latitude between 37.6075086377 and 40.5060593623 
               GROUP BY business.id 
               ORDER BY distance) As temp 
Group By category_id 
ORDER BY distance 
LIMIT 18

代码运行和执行得很好，但只需要一秒钟多一点就能完成(通常大约1.1秒)。然而，有人告诉我，在某些浏览器中页面加载速度很慢。我已经测试了多个浏览器和这些浏览器的多个版本，没有看到任何问题。然而，我认为如果我能降低执行时间，无论如何都会有所帮助。问题是我不知道我还能做些什么来缩短执行时间。邮政编码表已经带有预设索引，我认为这些索引很好(并且包含我在查询中使用的列)。我还向业务表添加了索引，尽管我不太了解它们。但是我已经确保至少包括Where子句中使用的字段，也许还有更多。

如果我需要添加索引到这个问题，请告诉我。如果你在查询中看到我可以改进的东西，也请告诉我。

谢谢,詹姆斯。

编辑

business表结构:

CREATE TABLE IF NOT EXISTS `business` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `active` tinyint(3) unsigned NOT NULL,
  `featured` enum('yes','no') NOT NULL DEFAULT 'yes',
  `topFeatured` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `category_id` smallint(5) NOT NULL DEFAULT '0',
  `listZip` varchar(12) NOT NULL,
  `name` tinytext NOT NULL,
  `address` tinytext NOT NULL,
  `city` varchar(128) NOT NULL,
  `state` varchar(32) NOT NULL DEFAULT '',
  `zip` varchar(12) NOT NULL,
  `phone` tinytext NOT NULL,
  `alt_phone` tinytext NOT NULL,
  `website` tinytext NOT NULL,
  `logo` tinytext NOT NULL,
  `index_logo` tinytext NOT NULL,
  `large_image` tinytext NOT NULL,
  `description` text NOT NULL,
  `views` int(5) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `featured` (`featured`,`topFeatured`,`category_id`,`listZip`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3085 ;

SQL小提琴

http://sqlfiddle.com/!2/2e26ff/1

编辑2014-03-26 09:09

我已经更新了我的查询，但是每次执行较短的查询实际上要多花0.2秒。

Select * From (
    SELECT Distinct business.id, business.name, business.large_image, business.logo, business.address, business.city, business.state, business.zip, business.phone, business.alt_phone, business.website, business.description, zipCodes.longitude, zipCodes.latitude, (3956 * 2 * ASIN ( SQRT (POWER(SIN((zipCodes.latitude - 39.056784)*pi()/180 / 2),2) + COS(zipCodes.latitude* pi()/180) * COS(39.056784 *pi()/180) * POWER(SIN((zipCodes.longitude - -84.343573) *pi()/180 / 2), 2) ) )) as distance 
    FROM business 
    INNER JOIN zipCodes ON (business.listZip = zipCodes.zipCode) 
    Where business.active = 1 
    And zipCodes.longitude between -86.2099407074 and -82.4772052926 
    And zipCodes.latitude between 37.6075086377 and 40.5060593623 
    GROUP BY business.category_id 
    HAVING distance <= '50'
    ORDER BY distance
) As temp LIMIT 18

在邮政编码数据库中已经有一个关于邮政编码、纬度和经度字段的索引，它们都在一个索引中，每个都有自己的索引。这张桌子刚买的时候就是这样的。

昨天我已经更新了listZip数据类型以匹配邮政编码表的zip数据类型。

我确实取出了GROUP BY business.id并用DISTINCT代替它，但留下了GROUP BY business.category_id，因为我每个类别只想要一个业务。

同样，当我将查询更改为使用HAVING子句而不是WHERE子句中的数学公式时，我开始得到0.2秒的执行差异。我确实尝试在外部查询中使用WHERE distance <= 50，但这也没有加快任何速度。同样，使用50英里而不是100英里似乎也不会影响这个特定的查询。

谢谢大家的建议

Answer 1

在zipCodes.longitude和zipCodes.latitude上放置索引。这应该很有帮助。

查看这里了解更多信息。http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

编辑您需要单独在longitude或从longitude开始的zipCodes表中添加索引。在我看来，你应该尝试在

上创建一个综合索引。

 (longitude, latitude, zipCode)

为最佳效果。

创建zipCodes的数据类型。邮编和商业。listingZip相同，因此连接将更有效。如果这些数据类型不同，MySQL在进行连接时将一种数据类型转换为另一种数据类型，因此连接将是低效的。确保生意。listingZip有一个索引。

你在滥用GROUP BY。(你是说SELECT DISTINCT吗?)这是没有意义的，除非您还使用像MAX()这样的聚合函数。以类似的方式，看看您是否可以在SELECT business.*中去掉*，而是给出您需要的列的列表。

100英里是一个非常宽的搜索半径。把它收窄一点，加快速度。

你计算了两次大圆距离。您当然可以重铸查询来完成一次。