1

I'm massaging some traffic data. Most files are small, but some run over 100Mb.

I want just to get the stops for each route. To do that (grind teeth), I have to compare all the major files as bits of data are spread throughout.

The query works but by the time the processing is finished, the customer has missed most busses.

This is the query, and any help is appreciated:

SET SQL_BIG_SELECTS=1;
SELECT DISTINCT

stops.stop_name,
trips.route_id,
stops.stop_id,
trips.direction_id,
stop_times.stop_sequence,
routes.route_long_name

FROM stop_times 

LEFT JOIN stops  USING (stop_id)
LEFT JOIN trips  USING (trip_id)
LEFT JOIN routes USING (route_id)

WHERE routes.route_id = 'xxx'

ORDER BY trips.direction_id, stop_times.stop_sequence ASC

As requested, these are the relevant table definitions:

routes

route_id routename

stops

stop_id stopname

trips

route_id trip_id

times

stop_id trip_id

These are the fields that link to each other... There are text fields in each table of course.

ICL1901
  • 7,632
  • 14
  • 90
  • 138
  • Table definitions or what indexes do you have? – Mihai Nov 05 '15 at 18:36
  • I'll edit the question. – ICL1901 Nov 05 '15 at 18:37
  • http://stackoverflow.com/a/898714/1745672 – Mihai Nov 05 '15 at 18:45
  • That question only refers to the db construction. I have the structure and data in place.. Can you see anywhere where that could be wrong? I've also looked here, where I borrowed copiously: `http://stackoverflow.com/questions/25750057/how-can-i-make-my-gtfs-queries-run-faster` – ICL1901 Nov 05 '15 at 18:48
  • 1
    FYI, GTFS is just an interchange format. If this is a typical query for your app, indexes or pre-caching are the way to go. – Tony Laidig Nov 05 '15 at 19:16
  • 1
    Taking my pointy hat off, you can further constrain the query by looking at the service_id of that date and adding trips.service_id to the where clause. – Tony Laidig Nov 05 '15 at 19:18
  • Thanks very much. The data isn't very good, so service_id is not a option. However, indexing the key fields certainly helped -- a lot. I'll read up to see if I can do more along those lines. If you want to make an answer, please go ahead. – ICL1901 Nov 05 '15 at 20:17

0 Answers0