I'm trying to execute a query on MySQL (Database created from a GTFS schema) I have two tables like these:


trip_id   |  route_id
1         |  409
2         |  409
3         |  409
4         |  409
5         |  671
6         |  671
7         |  671
8         |  105
9         |  105L
10        |  105L
11        |  105L

The other table:


trip_id   |  stop_id
1         |  A
2         |  B
3         |  C
4         |  C
5         |  A
6         |  A
7         |  B
8         |  A
9         |  B
10        |  C
11        |  D

I'm trying to join those tables to find all stops with every route id associated, without duplicates, like this:


stop_id   |  route_id
A         |  409
A         |  671
A         |  105
B         |  409
B         |  671
B         |  105L
C         |  409
C         |  105L
D         |  105L

The problem is that those tables are huge, trips contains 200k rows while stop_times contains about 6M rows.

The query that I tried is:

SELECT DISTINCT trips.route_id, stop_times.stop_id 
FROM trips 
LEFT JOIN stop_times 
  USING (trip_id) 
ORDER BY stop_times.stop_id

Explain output is:

 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trips
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 201128
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: stop_times
         type: ref
possible_keys: trip_id
          key: trip_id
      key_len: 52
          ref: muoviroma.trips.trip_id
         rows: 13
        Extra: NULL
2 rows in set (0,00 sec)


mysql> SHOW CREATE TABLE trips\G
*************************** 1. row ***************************
       Table: trips
Create Table: CREATE TABLE `trips` (
  `route_id` varchar(20) NOT NULL,
  `service_id` varchar(20) NOT NULL,
  `trip_id` varchar(50) NOT NULL,
  `direction_id` bigint(20) NOT NULL,
  `shape_id` bigint(20) NOT NULL,
  KEY `trip_id` (`trip_id`),
  KEY `route_id` (`route_id`),
  KEY `service_id` (`service_id`),
  KEY `direction_id` (`direction_id`),
  KEY `shape_id` (`shape_id`)
1 row in set (0,00 sec)

mysql> SHOW CREATE TABLE stop_times\G
*************************** 1. row ***************************
       Table: stop_times
Create Table: CREATE TABLE `stop_times` (
  `trip_id` varchar(50) NOT NULL,
  `arrival_time` time DEFAULT NULL,
  `departure_time` time DEFAULT NULL,
  `stop_id` varchar(20) NOT NULL,
  `stop_sequence` int(11) NOT NULL,
  KEY `trip_id` (`trip_id`),
  KEY `stop_id` (`stop_id`)
1 row in set (0,00 sec)

Those queries are performing in about 20 seconds, I'm searching a query that performs in less than 500ms, what can I try?

  • You're fetching every row from two very large tables, I don't see how you could speed this up. Most of the time taken is I/O. Only if you delete the ORDER BY things could get better but 500ms is not realistic – foibs Dec 16 '13 at 16:52
  • removing the order by (I already tried) makes things worse or doesn't provide any improvement! I'm getting 19-25 seconds performance without Order By. Given that dataset is updated about weekly, i could execute the query just once inside a support table, but i'd like to optimize it. There's absolutely no way? result set is way way shorter than 6 milions. – Luca Vitucci Dec 16 '13 at 17:01
  • I see a proper index on stop_times.trip_id and no where clause for the trips table, so maybe there's not much to optimize. Do you need to fetch ALL rows from trips, even those that don't have a stop? If not, you could try inner join. Also, out of curiosity, how long does it take to just select * from trips (all 200K rows) and how many rows are fetched from your left join query? – foibs Dec 16 '13 at 17:10
  • i don't need trips without stop, i need to obtain the list of stops with associated routes. select * from trips returns in about 290 mS – Luca Vitucci Dec 16 '13 at 17:14
  • If I understand correctly, you don't need a left join but an inner join. This will leave the distinct with less rows to handle. Also, a suggestion is to let the query planner determine : SELECT DISTINCT trips.route_id, stop_times.stop_id FROM trips, stop_times WHERE trips.trip_id = stop_times.trip_id ORDER BY stop_times.stop_id – foibs Dec 16 '13 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.