mysql - 开始和结束(靠近)mysql中的路由

Question

我正在研究一个搜索系统，该系统应该检测起点和终点是否在(近 50 公里)路线上。我有许多路线作为点 [300k 行] 存储在 mysql 数据库中。

Structure 
id [primary] | id_route | id_point | lat_lng_point (spatial index) 
1              1          1          [GEOMETRY - 25 B]
2              1          2          [GEOMETRY - 25 B]
3              1          3          [GEOMETRY - 25 B]
4              1          4          [GEOMETRY - 25 B]
5              2          1          [GEOMETRY - 25 B]
6              2          2          [GEOMETRY - 25 B]
...            ...        ...        ...    

问题是如何最有效地选择起点和终点(或接近 50 公里)的路线 (route_id)？

我已经尝试过 union [in example](或 inner join)，它可以工作，但是查询需要大约 0.4s，这太多了。 知道如何优化吗？

SELECT * FROM 
        (
            (
                SELECT DISTINCT(id_route)
                FROM route_path2
                WHERE ST_Contains( ST_MakeEnvelope(
                                    Point(($lng_start+(50/111)), ($lat_start+(50/111))),
                                    Point(($lng_start-(50/111)), ($lat_start-(50/111)))
                                 ), route_path2.lat_lng_point )
            )
        UNION ALL
            (
                SELECT DISTINCT(id_route)
                FROM route_path2
                WHERE ST_Contains( ST_MakeEnvelope(
                                    Point(($lng_end+(50/111)), ($lat_end+(50/111))),
                                    Point(($lng_end-(50/111)), ($lat_end-(50/111)))
                                 ), route_path2.lat_lng_point )
            ) 

        ) AS t GROUP BY id_route HAVING count(*) >= 2 

编辑:

我根据@Djeramon 的建议进行了优化，现在 0.06s 我不知道这是我能达到的最好结果，如果我有 5000 万行会怎样:)

CREATE TEMPORARY TABLE starts_on_route AS
SELECT DISTINCT id_route
FROM route_path2
WHERE ST_Contains( ST_MakeEnvelope(
                    Point((17.1077+(50/111)), (48.1486+(50/111))),
                    Point((17.1077-(50/111)), (48.1486-(50/111)))
                 ), route_path2.lat_lng_point );

CREATE INDEX starts_on_route_inx ON starts_on_route(id_route);

SELECT DISTINCT route_path2.id_route
FROM route_path2
LEFT JOIN starts_on_route 
ON route_path2.id_route = starts_on_route.id_route
WHERE ST_Contains( ST_MakeEnvelope(
    Point((18.7408+(50/111)), (49.2194+(50/111))),
    Point((18.7408-(50/111)), (49.2194-(50/111)))
), lat_lng_point )
AND route_path2.id_route = starts_on_route.id_route;

Answer 1

目前您正在对整个路由表运行查询两次。尝试运行第一个子查询以确定具有有效起点的所有路线，并仅在这些相关路线上运行第二个子查询。这应该可以节省大约 50% 的处理时间。

一种方法是使用临时表来存储第一个查询的结果。但是，您需要注意创建的开销，为它创建索引可能是个好主意。有关更多详细信息，请参阅 http://blog.endpoint.com/2015/02/temporary-tables-in-sql-query.html