Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

trips

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:

stop_times

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:

result

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)

SHOW CREATE TABLE outputs:

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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?

share|improve this question
    
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
show 3 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.