Why the same query can take 1000 times as long depending on the where clause?

Question

I have a rather big query that collects a lot of information in several tables, using joins. The database is a GTFS transit information from a city public transportation system.

I'm running the same query with a different WHERE clause, and the time taken can go from 200ms to 200s.

If you don't need the explanation, scroll down straight to The problem.

The database

The tables are:

routes
trips: connected to routes using route_id
stop_times: connected totripsusingtrip_id`
stops: connected to stop_times using stop_id
stop_connections: connects two stop_id

My goal is to select journeys using 2 connections. Here's how my query looks on paper:

Query schema written on paper

Explanation:

The black information is the tables, one table type per line (ie. top line is the trips table).
the red information is the alias in my query (s is stops, st is stop_times, t is trips, a is arrival, d is departure, and the 1/2/3 is the trip index)
the green information is the list of conditions on each table

Basically it's:

[s1d ] Start from a given stop_id
[st1d] Get departure time of trips from that stop
[t1 ] Limit those trips to the set of route_id we want
[st1a] Get the arrival time of the stop
[s1a ] Get stop information (stop name)
[cs1 ] Connect this stop to all other stops in a walking distance

Repeat this operation 3 times to get 3 trips (2 connections), and filter the arrival stops to the one that I want.

The query

select 

    s1d.stop_id as s1d_id, s1d.stop_name as s1d_name, s1d.stop_lat as s1d_lat, s1d.stop_lon as s1d_lon, 
    st1d.departure_time as st1d_dep, 
    t1.trip_id as t1_id, t1.trip_headsign as t1_headsign, t1.route_id as t1_route, t1.direction_id as t1_dir, 
    st1a.departure_time as st1a_dep, 
    s1a.stop_id as s1a_id, s1a.stop_name as s1a_name, s1a.stop_lat as s1a_lat, s1a.stop_lon as s1a_lon, 
    cs1.from_stop_id, cs1.to_stop_id, 
    s2d.stop_id as s2d_id, s2d.stop_name as s2d_name, s2d.stop_lat as s2d_lat, s2d.stop_lon as s2d_lon,  
    st2d.departure_time as st2d_dep, 
    t2.trip_id as t2_id, t2.trip_headsign as t2_headsign, t2.route_id as t2_route, t2.direction_id as t2_dir, 
    st2a.departure_time as st2a_dep, 
    s2a.stop_id as s2a_id, s2a.stop_name as s2a_name, s2a.stop_lat as s2a_lat, s2a.stop_lon as s2a_lon, 
    cs2.from_stop_id, cs2.to_stop_id, 
    s3d.stop_id as s3d_id, s3d.stop_name as s3d_name, s3d.stop_lat as s3d_lat, s3d.stop_lon as s3d_lon,  
    st3d.departure_time as st3d_dep, 
    t3.trip_id as t3_id, t3.trip_headsign as t3_headsign, t3.route_id as t3_route, t3.direction_id as t3_dir, 
    st3a.departure_time as st3a_dep, 
    s3a.stop_id as s3a_id, s3a.stop_name as s3a_name, s3a.stop_lat as s3a_lat, s3a.stop_lon as s3a_lon

from stops s1d

left join stop_times st1d on st1d.stop_id = s1d.stop_id     
    and st1d.departure_time > '07:33:00' and st1d.departure_time < '08:33:00'

left join trips t1 on t1.trip_id = st1d.trip_id     
    and t1.service_id in (select service_id from calendar where start_date <= 20141020 and end_date >= 20141020 and monday = 1) 
    and t1.route_id in ('11-0') 

left join stop_times st1a on st1a.trip_id = t1.trip_id      
    and st1a.departure_time > st1d.departure_time

left join stops s1a on s1a.stop_id = st1a.stop_id 

left join stop_connections cs1 on cs1.from_stop_id = st1a.stop_id 

left join stops s2d on s2d.stop_id = cs1.to_stop_id 

left join stop_times st2d on st2d.stop_id = s2d.stop_id 
    and st2d.departure_time > addtime(st1a.departure_time, '00:03:00')
    and st2d.departure_time < addtime(st1a.departure_time, '01:03:00')

left join trips t2 on t2.trip_id = st2d.trip_id     
    and t2.service_id in (select service_id from calendar where start_date <= 20141020 and end_date >= 20141020 and monday = 1) 
    and t2.route_id in ('3-0', 'NA-0', '4-0', '2-0') 

left join stop_times st2a on st2a.trip_id = t2.trip_id and st2a.departure_time > st2d.departure_time 

left join stops s2a on s2a.stop_id = st2a.stop_id 

left join stop_connections cs2 on cs2.from_stop_id = st2a.stop_id 

left join stops s3d on s3d.stop_id = cs2.to_stop_id 

left join stop_times st3d on st3d.stop_id = s3d.stop_id         
    and st3d.departure_time > addtime(st2a.departure_time, '00:03:00')
    and st3d.departure_time < addtime(st2a.departure_time, '01:03:00')

left join trips t3 on t3.trip_id = st3d.trip_id 
    and t3.service_id in (select service_id from calendar where start_date <= 20141020 and end_date >= 20141020 and monday = 1) 
    and t3.route_id in ('36-0', '30-0', '97-0')
    left join stop_times st3a on st3a.trip_id = t3.trip_id 

    and st3a.departure_time > st3d.departure_time 

    and st3a.stop_id in ('StopPoint:CLBO2',
     'StopArea:CLBO',
     'StopPoint:CLBO1',
     'StopPoint:PLTI2',
     'StopPoint:LCBU2',
     'StopArea:LCBU',
     'StopPoint:LCBU1',
     'StopPoint:MHDI2',
     'StopPoint:BILE2',
     'StopArea:MHDI',
     'StopPoint:MHDI1',
     'StopPoint:MREZ2',
     'StopArea:MRDI',
     'StopPoint:MRDI1',
     'StopArea:SORI',
     'StopPoint:SORI1',
     'StopArea:MREZ',
     'StopPoint:MREZ1',
     'StopPoint:SORI2',
     'StopArea:BILE',
     'StopPoint:BILE1',
     'StopPoint:MRDI2',
     'StopArea:PLTI',
     'StopPoint:PLTI1',
     'StopPoint:SEIL3',
     'StopPoint:SEIL2',
     'StopArea:SEIL',
     'StopPoint:SEIL1')
    left join stops s3a on s3a.stop_id = st3a.stop_id

where s1d.stop_id = 'StopPoint:DEMO1'

group by s1d_id, s3a_id
having s3a_id is not null
order by s1d_id asc, st1d_dep asc, st1a_dep asc, s1a_id asc, s2d_id asc, st2d_dep asc, st2a_dep asc, s2a_id asc, s3d_id asc, st3d_dep asc, st3a_dep asc, s3a_id asc

The problem

I run this query twice, the only difference is in the where clause at the end:

where s1d.stop_id = 'StopPoint:DEMO1': 13 rows in set (2 min 58.81 sec)
where s1d.stop_id = 'StopPoint:ECTE2': Empty set (0.25 sec)

Now that's very strange to me. Here's the explain for both queries:

Departing from DEMO1 (13 results, slow)

Using EXPLAIN SELECT…:

+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+-------------+
| id | select_type | table    | type   | possible_keys                             | key              | key_len | ref                              | rows | Extra       |
+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+-------------+
|  1 | SIMPLE      | s1d      | ALL    | NULL                                      | NULL             | NULL    | NULL                             | 3411 | NULL        |
|  1 | SIMPLE      | st1d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | bicou_gtfs_nantes.s1d.stop_id    |  163 | Using where |
|  1 | SIMPLE      | t1       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st1d.trip_id   |    1 | Using where |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t1.service_id  |    1 | Using where |
|  1 | SIMPLE      | st1a     | ref    | st_trip_id_idx                            | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t1.trip_id     |   14 | Using where |
|  1 | SIMPLE      | s1a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st1a.stop_id   |    1 | NULL        |
|  1 | SIMPLE      | cs1      | ref    | from_to_stop_ids                          | from_to_stop_ids | 302     | bicou_gtfs_nantes.st1a.stop_id   |    1 | Using index |
|  1 | SIMPLE      | s2d      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.cs1.to_stop_id |    1 | NULL        |
|  1 | SIMPLE      | st2d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | bicou_gtfs_nantes.s2d.stop_id    |  163 | Using where |
|  1 | SIMPLE      | t2       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st2d.trip_id   |    1 | Using where |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t2.service_id  |    1 | Using where |
|  1 | SIMPLE      | st2a     | ref    | st_trip_id_idx                            | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t2.trip_id     |   14 | Using where |
|  1 | SIMPLE      | s2a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st2a.stop_id   |    1 | NULL        |
|  1 | SIMPLE      | cs2      | ref    | from_to_stop_ids                          | from_to_stop_ids | 302     | bicou_gtfs_nantes.st2a.stop_id   |    1 | Using index |
|  1 | SIMPLE      | s3d      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.cs2.to_stop_id |    1 | NULL        |
|  1 | SIMPLE      | st3d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | bicou_gtfs_nantes.s3d.stop_id    |  163 | Using where |
|  1 | SIMPLE      | t3       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st3d.trip_id   |    1 | Using where |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t3.service_id  |    1 | Using where |
|  1 | SIMPLE      | st3a     | ref    | st_stop_id_idx,st_trip_id_idx             | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t3.trip_id     |   14 | Using where |
|  1 | SIMPLE      | s3a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st3a.stop_id   |    1 | NULL        |
+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+-------------+

Using EXPLAIN EXTENDED…:

+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+----------+---------------------------------+
| id | select_type | table    | type   | possible_keys                             | key              | key_len | ref                              | rows | filtered | Extra                           |
+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | s1d      | const  | PRIMARY                                   | PRIMARY          | 302     | const                            |    1 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | st1d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | const                            |  234 |   100.00 | Using where                     |
|  1 | SIMPLE      | t1       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st1d.trip_id   |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t1.service_id  |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | st1a     | ref    | st_trip_id_idx                            | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t1.trip_id     |   14 |   100.00 | Using where                     |
|  1 | SIMPLE      | s1a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st1a.stop_id   |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | cs1      | ref    | from_to_stop_ids                          | from_to_stop_ids | 302     | bicou_gtfs_nantes.st1a.stop_id   |    1 |   100.00 | Using index                     |
|  1 | SIMPLE      | s2d      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.cs1.to_stop_id |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | st2d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | bicou_gtfs_nantes.s2d.stop_id    |  163 |   100.00 | Using where                     |
|  1 | SIMPLE      | t2       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st2d.trip_id   |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t2.service_id  |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | st2a     | ref    | st_trip_id_idx                            | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t2.trip_id     |   14 |   100.00 | Using where                     |
|  1 | SIMPLE      | s2a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st2a.stop_id   |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | cs2      | ref    | from_to_stop_ids                          | from_to_stop_ids | 302     | bicou_gtfs_nantes.st2a.stop_id   |    1 |   100.00 | Using index                     |
|  1 | SIMPLE      | s3d      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.cs2.to_stop_id |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | st3d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | bicou_gtfs_nantes.s3d.stop_id    |  163 |   100.00 | Using where                     |
|  1 | SIMPLE      | t3       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st3d.trip_id   |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t3.service_id  |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | st3a     | ref    | st_stop_id_idx,st_trip_id_idx             | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t3.trip_id     |   14 |   100.00 | Using where                     |
|  1 | SIMPLE      | s3a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st3a.stop_id   |    1 |   100.00 | NULL                            |
+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+----------+---------------------------------+

Departing from ECTE2 (0 result, fast)

+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+---------------------------------+
| id | select_type | table    | type   | possible_keys                             | key              | key_len | ref                              | rows | Extra                           |
+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+---------------------------------+
|  1 | SIMPLE      | s1d      | const  | PRIMARY                                   | PRIMARY          | 302     | const                            |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | st1d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | const                            |  234 | Using where                     |
|  1 | SIMPLE      | t1       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st1d.trip_id   |    1 | Using where                     |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t1.service_id  |    1 | Using where                     |
|  1 | SIMPLE      | st1a     | ref    | st_trip_id_idx                            | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t1.trip_id     |   14 | Using where                     |
|  1 | SIMPLE      | s1a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st1a.stop_id   |    1 | NULL                            |
|  1 | SIMPLE      | cs1      | ref    | from_to_stop_ids                          | from_to_stop_ids | 302     | bicou_gtfs_nantes.st1a.stop_id   |    1 | Using index                     |
|  1 | SIMPLE      | s2d      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.cs1.to_stop_id |    1 | NULL                            |
|  1 | SIMPLE      | st2d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | bicou_gtfs_nantes.s2d.stop_id    |  163 | Using where                     |
|  1 | SIMPLE      | t2       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st2d.trip_id   |    1 | Using where                     |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t2.service_id  |    1 | Using where                     |
|  1 | SIMPLE      | st2a     | ref    | st_trip_id_idx                            | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t2.trip_id     |   14 | Using where                     |
|  1 | SIMPLE      | s2a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st2a.stop_id   |    1 | NULL                            |
|  1 | SIMPLE      | cs2      | ref    | from_to_stop_ids                          | from_to_stop_ids | 302     | bicou_gtfs_nantes.st2a.stop_id   |    1 | Using index                     |
|  1 | SIMPLE      | s3d      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.cs2.to_stop_id |    1 | NULL                            |
|  1 | SIMPLE      | st3d     | ref    | st_stop_id_idx                            | st_stop_id_idx   | 302     | bicou_gtfs_nantes.s3d.stop_id    |  163 | Using where                     |
|  1 | SIMPLE      | t3       | eq_ref | PRIMARY,trip_service_id,trip_route_id_idx | PRIMARY          | 302     | bicou_gtfs_nantes.st3d.trip_id   |    1 | Using where                     |
|  1 | SIMPLE      | calendar | eq_ref | PRIMARY,service_id                        | PRIMARY          | 302     | bicou_gtfs_nantes.t3.service_id  |    1 | Using where                     |
|  1 | SIMPLE      | st3a     | ref    | st_stop_id_idx,st_trip_id_idx             | st_trip_id_idx   | 302     | bicou_gtfs_nantes.t3.trip_id     |   14 | Using where                     |
|  1 | SIMPLE      | s3a      | eq_ref | PRIMARY                                   | PRIMARY          | 302     | bicou_gtfs_nantes.st3a.stop_id   |    1 | NULL                            |
+----+-------------+----------+--------+-------------------------------------------+------------------+---------+----------------------------------+------+---------------------------------+

Obviously the engine handles the two queries differently. Now why is a different question.

The s1d object is from the table stops:

CREATE TABLE IF NOT EXISTS `stops` (
  `stop_id` VARCHAR(100) NOT NULL,
  `stop_code` VARCHAR(50) NULL DEFAULT NULL,
  `stop_name` VARCHAR(255) NOT NULL,
  `stop_desc` VARCHAR(255) NULL DEFAULT NULL,
  `stop_lat` DECIMAL(10,6) NOT NULL,
  `stop_lon` DECIMAL(10,6) NOT NULL,
  `zone_id` VARCHAR(255) NULL DEFAULT NULL,
  `stop_url` VARCHAR(255) NULL DEFAULT NULL,
  `location_type` VARCHAR(2) NULL DEFAULT NULL,
  `parent_station` VARCHAR(100) NOT NULL,
  `stop_timezone` VARCHAR(50) NULL DEFAULT NULL,
  `wheelchair_boarding` TINYINT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`stop_id`),
  INDEX `zone_id` (`zone_id` ASC),
  INDEX `stop_lat` (`stop_lat` ASC),
  INDEX `stop_lon` (`stop_lon` ASC),
  INDEX `location_type` (`location_type` ASC),
  INDEX `parent_station` (`parent_station` ASC),
  CONSTRAINT `stop_parent_station`
    FOREIGN KEY (`parent_station`)
    REFERENCES `stops` (`stop_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

I don't understand why when there is no data the engine properly uses indexes and keys; and when there is data (13 rows), the engine doesn't use the indexes and keys and browses 3 thousand rows instead of one.

Is there any way for me to force the engine to use keys on specific tables?
Also, why is the engine behaving like this?

Environment:

OS: Mac OS X 10.10
SQL client: mysql Ver 14.14 Distrib 5.6.17, for osx10.6 (i386) using EditLine wrapper
SQL server: 5.6.21 MySQL Community Server (GPL)
Hardware: MacBook Air, Intel Core i7, 8GB RAM, 256GB SSD (should be fast)

Table sizes:

+-------------------+------------+
| table_name        | TABLE_ROWS |
+-------------------+------------+
| agency            |          0 |
| calendar          |         28 |
| calendar_dates    |       1005 |
| fare_attributes   |          0 |
| fare_rules        |          0 |
| feed_info         |          0 |
| frequencies       |          0 |
| route_connections |      20919 |
| routes            |         60 |
| shapes            |          0 |
| stop_connections  |      11617 |
| stop_times        |     768682 |
| stops             |       3411 |
| stops_routes      |      16652 |
| transfers         |          0 |
| trips             |      31913 |
+-------------------+------------+

Row count after each joined table:

+---------+-------------+------------+
| Table   |  DEMO1      |   ECTE2    |
+---------+-------------+------------+
| s1d     |           1 |          1 |
| st1d    |          16 |         18 |
| t1      |          16 |         18 |
| st1a    |         271 |        117 |
| s1a     |         271 |        117 |
| cs1     |        1286 |        495 |
| s1d     |        1286 |        495 |
| st2d    |       32958 |       5973 |
| t2      |       32958 |       5973 |
| st2a    |       65891 |       5973 |
| s2a     |       65891 |       5973 |
| cs2     |      206455 |       5973 |
| s3d     |      206455 |       5973 |
| st3d    |     4284871 |       5973 |
| t3      |     4284871 |       5973 |
| st3a    |     4351249 |       5973 |
| s3a     |     4351249 |       5973 |
| +having |          13 |          0 |
+---------+-------------+------------+

How many records do you have that match `'StopPoint:DEMO1'` and how many that match `'StopPoint:ECTE2'` in general (entire table)? Simple `COUNT()` would do it. — Kamil Gosciminski, Oct 18 '14 at 10:56
I'm not sure in which table you want the count from. `select count(*) from stop_times where stop_id = ?` yields 235 for both `DEMO1` and `ECTE2`. — Benoit Duffez, Oct 18 '14 at 10:58
`stop_id` is the primary key of `stops`, so there's only one row for each. — Benoit Duffez, Oct 18 '14 at 11:04
Yes, my bad. Does from stops s1d `select * from stops s1d left join stop_times st1d on st1d.stop_id = s1d.stop_id and st1d.departure_time > '07:33:00' and st1d.departure_time < '08:33:00' where s1d.stop_id = ...` produce big difference in rows when you put DEMO1/ECTE2? — Kamil Gosciminski, Oct 18 '14 at 11:09
Doesn't this give you the answer? (number of rows being fetched) — Kamil Gosciminski, Oct 18 '14 at 12:17
This explains the response time, not the used keys (see the result of the `EXPLAIN`) — Benoit Duffez, Oct 18 '14 at 12:19

score 1 · Answer 1 · answered Oct 18 '14 at 11:52

1

Two thoughts come in mind:

1) Switch some indices to BTEE indices. The default is HASH which are good for equal/unequal comparison, not IN(...). See here

2) See what the optimizer made with your queries. Do a

EXPLAIN EXTENDED SELECT ...

on both queries. THis will give you a warning containg the query optimizer output. You should see a difference here.

answered Oct 18 '14 at 11:52

Benvorth

7,416
8
49
70

I'm using InnoDB, and AFAIK the indices already use `BTREE`. I have posted the result of `EXPLAIN EXTENDED SELECT ...`, however now it's showing that it will use the key for `s1d`. I'm confused. – Benoit Duffez Oct 18 '14 at 15:26
hmmm... Do you do a lot of `UPDATES` on the table? Perhaps updating the index statistics will help the optimizer to produce stable results? `OPTIMIZE TABLE myTable` – Benvorth Oct 19 '14 at 07:42
The table is filled once from a csv file. `Table does not support optimize, doing recreate + analyze instead`, then the analyze resulted in `OK`. – Benoit Duffez Oct 19 '14 at 07:57
I think the good news ist that your question is answerd: The same query takes more time because the optimizer comes to different conclusions about the best execution plan. Bad news is that there should definitely be no difference between the result of `EXPLAIN` and `EXPLAIN EXTENDED`... – Benvorth Oct 19 '14 at 08:07