mysql - Mysql上的gtfs数据库-在区域中搜索路由时查询缓慢

我目前正在运行带有GTFS数据的mysql数据库。
我正在尝试获取特定区域（距中心点5公里）的所有路线。
这个想法是查询特定区域的站点，然后从站点查询stop_times和trips以获取路线。
我的问题是stop_times表确实很大，我找不到正确的SQL查询以避免慢查询。

我试过普通的内部联接查询，然后用不同的键进行子查询：

SELECT routes.*
FROM routes
WHERE routes.route_id IN
(SELECT DISTINCT trips.route_id FROM trips WHERE trips.trip_id IN
(SELECT DISTINCT stop_times.trip_id FROM stop_times WHERE stop_times.stop_id IN
(SELECT DISTINCT stops.stop_id FROM stops WHERE ST_DISTANCE_SPHERE(POINT(9.191383,45.464210), stops.coordinate) < 5000)))

建议？

最佳答案

尝试避免使用IN子句，而改用INNER JOIN

SELECT routes.*
FROM routes
INNER JOIN (
  SELECT DISTINCT trips.route_id
  FROM trips
  INNER JOIN  (
    SELECT DISTINCT stop_times.trip_id
    FROM stop_times
    INNER JOIN (
      SELECT DISTINCT stops.stop_id
      FROM stops
      WHERE ST_DISTANCE_SPHERE(POINT(9.191383,45.464210), stops.coordinate) < 5000
    ) t1 on t1.stop_id = stop_times.stop_id
  ) t2 ON t2.trip_id = trips.trip_id
) t3 ON routes.route_id = t3.route_id

并确保您有索引

table routes  column route_id
table trips column trip_id
table stop_times column stop_id