mysql lat lon,需要帮助优化lat / Lon geo搜索mysql

我有一个mysql(5.0.22)myisam表,里面有大约300k记录,我想在5英里范围内进行纬度/离子距离搜索.

我有一个覆盖lat / lon字段的索引,当我选择lat / lon时,它是快速的(毫秒响应).但是当我选择表格中的其他字段时,可能会慢慢减慢到5-8秒.

我正在使用myisam来利用全文搜索.其他索引表现良好(例如,从列表中选择*,其中slug =’xxxxx’).

如何优化查询,表格或索引以加快速度？

我的架构是：

CREATE TABLE `Listing` (

`id` int(10) unsigned NOT NULL auto_increment,

`name` varchar(125) collate utf8_unicode_ci default NULL,

`phone` varchar(18) collate utf8_unicode_ci default NULL,

`fax` varchar(18) collate utf8_unicode_ci default NULL,

`email` varchar(55) collate utf8_unicode_ci default NULL,

`photourl` varchar(55) collate utf8_unicode_ci default NULL,

`thumburl` varchar(5) collate utf8_unicode_ci default NULL,

`website` varchar(85) collate utf8_unicode_ci default NULL,

`categoryid` int(10) unsigned default NULL,

`addressid` int(10) unsigned default NULL,

`deleted` tinyint(1) default NULL,

`status` int(10) unsigned default '2',

`parentid` int(10) unsigned default NULL,

`organizationid` int(10) unsigned default NULL,

`listinginfoid` int(10) unsigned default NULL,

`createuserid` int(10) unsigned default NULL,

`createdate` datetime default NULL,

`lasteditdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`lastedituserid` int(10) unsigned default NULL,

`slug` varchar(155) collate utf8_unicode_ci default NULL,

`aclid` int(10) unsigned default NULL,

`alt_address` varchar(80) collate utf8_unicode_ci default NULL,

`alt_website` varchar(80) collate utf8_unicode_ci default NULL,

`lat` decimal(10,7) default NULL,

`lon` decimal(10,7) default NULL,

`city` varchar(80) collate utf8_unicode_ci default NULL,

`state` varchar(10) collate utf8_unicode_ci default NULL,

PRIMARY KEY (`id`),

KEY `idx_fetch` USING BTREE (`slug`,`deleted`),

KEY `idx_loc` (`state`,`city`),

KEY `idx_org` (`organizationid`,`status`,`deleted`),

KEY `idx_geo_latlon` USING BTREE (`status`,`lat`,`lon`),

FULLTEXT KEY `idx_name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

我的查询是：

SELECT Listing.name, Listing.categoryid, Listing.lat, Listing.lon

, 3956 * 2 * ASIN(SQRT( POWER(SIN((Listing.lat - 37.369195) * pi()/180 / 2), 2) + COS(Listing.lat * pi()/180) * COS(37.369195 * pi()/180) * POWER(SIN((Listing.lon --122.036849) * pi()/180 / 2), 2) )) rawgeosearchdistance

FROM Listing

WHERE

Listing.status = '2'

AND ( Listing.lon between -122.10913433498 and -121.96456366502 )

AND ( Listing.lat between 37.296909665016 and 37.441480334984)

HAVING rawgeosearchdistance < 5

ORDER BY rawgeosearchdistance ASC;

解释没有geosearch的计划：

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra |

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+

| 1 | SIMPLE | Listing | range | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Using where |

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+

用geosearch解释计划：

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+

| 1 | SIMPLE | Listing | range | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Using where; Using filesort |

+----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+

这是覆盖索引的解释计划.以正确的顺序排列列有很大的不同：

+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+

| 1 | SIMPLE | Listing | range | idx_geo_cover | idx_geo_cover | 12 | NULL | 453 | Using where; Using index; Using filesort |

+----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+

谢谢！

解决方法:

您可能只在lat / lon查询中使用“覆盖索引”.当查询使用的索引包含您要选择的数据时,会出现覆盖索引. MySQL只需要访问索引而不需要访问数据行. See this for more info.这可以解释为什么lat / lon查询如此之快.

我怀疑计算和返回的行数,减慢了查询的时间. (加上必须为having子句创建的任何临时表).