使用纬度/经度索引mysql表以进行地理查找 [英] Indexing a mysql table for geo lookup using latitude/longitude

问题描述

我有一个遗留的innodb表列表，其中包含具有纬度/经度的业务。给定输入纬度/经度（低于51.2167 / 4.41667），查询将按接近度（公里）的顺序返回第一个活动的，启用的，未删除的30个业务。与帐户表的联接是为了检查列表的有效性。

I have a legacy innodb table Listing containing a business with latitude/longitude. Given an input latitude/longitude (below 51.2167/4.41667), the query is to return the first active, enabled, not-deleted 30 businesses in order of proximity (kilometers). A join with the accounts table is made to check the validity of the listing.

select 
    listing.*
from
    listing listing ,
    account account 
where
    listing.account_id = account.id 
    and listing.active = 1 
    and listing.approved = 1 
    and listing.deleted = 0 
    and listing.enabled = 1 
    and account.enabled = 1 
    and account.activated_by_user = 1 
group by
    listing.id
having
     111.222569*degrees(acos(sin(radians(listing.latitude))*sin(radians( 51.2167)) +cos(radians(listing.latitude))*cos(radians( 51.2167))*cos(radians(listing.longitude - 4.41667)))) < 250
order by
     111.222569*degrees(acos(sin(radians(listing.latitude))*sin(radians( 51.2167)) +cos(radians(listing.latitude))*cos(radians( 51.2167))*cos(radians(listing.longitude - 4.41667))))
limit 30;

表的列表和帐户每行包含超过50,000行，但查询仍然需要24秒才能运行。没有订单，需要17秒。

The table Listing and Account each contain over 50,000 rows, but the query still takes on average 24sec to run. Without the order by, it takes 17sec.

我已经尝试在活动，已批准，已删除，已启用的情况下设置一些索引。我可以重写查询或添加某些索引来有效地执行此查询 - 而不更改表结构吗？

I've already tried setting some indexes on active, approved, deleted, enabled. Can I rewrite the query or add certain indexes to efficiently perform this query - without changing the table structure?

+----+-------------+---------+-------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table   | type        | possible_keys                                                                                   | key                                                             | key_len | ref                    | rows | Extra                                                                                                                          |
+----+-------------+---------+-------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | listing | index_merge | FKB4DC521D9306A80C,listing_active,listing_approved,listing_enabled,listing_deleted,index_test_1 | listing_active,listing_approved,listing_enabled,listing_deleted | 1,1,1,1 | NULL                   | 3392 | Using intersect(listing_active,listing_approved,listing_enabled,listing_deleted); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | account | eq_ref      | PRIMARY,account_enabled,account_activated_by_user,index_test_2                                  | PRIMARY                                                         | 8       | ctm.listing.account_id |    1 | Using where                                                                                                                    |
+----+-------------+---------+-------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------------------------------------------+

非常感谢任何帮助。

推荐答案

这需要很长时间，因为你的查询是为你的50k行表中的每一行计算一个包含所有超越函数的大圆距离公式（当你包括排序时两次）。

This is taking a long time because your query is computing that great-circle-distance formula with all the transcendental functions once for each row in your 50k row table (twice when you include the sort).

可以你限制搜索的距离范围？您可能已经注意到，大多数商店查找器Web应用程序都有一个下拉菜单项，可以选择5英里内，10英里内，等等。

Can you limit the distance range of your search? You've probably noticed that most store-finder web apps have a pulldown menu item giving a choice "within 5 miles," "within 10 miles," and so forth.

如果你能这样做，你应该在你的搜索中添加一个WHERE子句，并通过在你的LATITUDE列上放一个索引来优化它。假设您使用RANGELIMIT值作为搜索范围限制，以英里为单位。

If you CAN do this, you should add a WHERE clause to your search, and optimize it by putting an index on your LATITUDE column. Let's say you use the value RANGELIMIT for your search range limit, in miles.

尝试此条款

WHERE LISTING.LATITUDE BETWEEN LOCATION.LATITUDE - (RANGELIMIT * 1.1508/60) 
                           AND LOCATION.LATITUDE + (RANGELIMIT * 1.1508/60)

这是因为海里几乎正好等于纬度的一分钟（1/60）。 1.1508因子将海里转换为法定里程。

This works because a nautical mile is almost exactly equal to one minute (1/60th) of a degree of latitude. The 1.1508 factor converts nautical miles into statute miles.

我建议的条款将使用纬度指数缩小搜索范围，并计算大圆距离a不太经常使用。

The clause I suggested will use a latitude index to narrow down the search, and you'll compute the great circle distance a whole lot less frequently.

您还可以在经度上包含BETWEEN子句。但根据我的经验，只需在纬度BETWEEN搜索中获得优异的结果。

You could also include a BETWEEN clause on longitude. But in my experience just doing the latitude BETWEEN search gets you excellent results.

这篇关于使用纬度/经度索引mysql表以进行地理查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！