Optimizing MySQL subquery

Question

Working with MTA API.

stop_times table looks like this: trip_id, stop_id

trip_id is repeated as stop_ids are listed per row. Example:

  1111, 1
  1111, 2
  1111, 3
  2222, 1
  2222, 3
  3333, 1
  3333, 2

Goal is to select trip_id of a train that we know will definitely stop at two specific stations. If we want trains that will stop at 1 and 3, we will get trips 1111, and 2222. Or if 1 and 2, then 1111 and 3333.

Here's what I wrote quickly, and of course it runs rather slow:

SELECT trip_id 
FROM stop_times 
WHERE stop_id=## 
  AND trip_id IN (SELECT trip_id FROM stop_times WHERE stop_id=##)

Basically, I am trying to do the equivalent of MS SQL INTERSECT.

How can I optimize this to run better?

Answer 1

select trip_id 
from stop_times 
where stop_id in (111, 222)
group by trip_id
having count(distinct stop_id) = 2

Answer 2

See this excellent answer on a variety of ways to accomplish this - plus performance tests:
how-to-filter-sql-results-in-a-has-many-through-relation

One way is this (assuming that the (trip_id, stop_id) combination is UNIQUE in your table):

SELECT a.trip_id 
FROM stop_times a
  JOIN stop_times b
    ON b.trip_id = a.trip_id
WHERE a.stop_id = #1 
  AND b.stop_id = #2

Answer 3

SELECT trip_id FROM stop_times WHERE stop_id IN (##,##)
HAVING count(DISTINCT stop_id)=2;