sql task

gistfile1.sql

SELECT i.route_id, min(t.cost) m 
FROM (SELECT DISTINCT route_id FROM tickets) AS i, tickets t 
WHERE i.route_id = t.route_id AND 
  city_from='ABC' AND 
  city_to='ABC' AND 
  date_direct='2012-05-24' AND 
  date_back='2012-05-25' AND
  expires > NOW()
  GROUP BY t.route_id
  ORDER BY m;
(about 0.20 sec with about 5 millions records and thousand of route_id)
+----+-------------+------------+-------+---------------+------+---------+------------------------------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref                          | rows | Extra                           |
+----+-------------+------------+-------+---------------+------+---------+------------------------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL                         | 1000 | Using temporary; Using filesort |
|  1 | PRIMARY     | t          | ref   | i1            | i1   | 17      | i.route_id,const,const,const |    1 | Using where                     |
|  2 | DERIVED     | tickets    | range | NULL          | i1   | 5       | NULL                         | 2674 | Using index for group-by        |
+----+-------------+------------+-------+---------------+------+---------+------------------------------+------+---------------------------------+
3 rows in set (0.01 sec)




MIN INDEX:
city_from, city_to, date_direct, date_back, expires, route_id