Query dramatically slower with less rows from WHERE to evaluate

Question

What is the query

SELECT SQL_NO_CACHE id, laptime, driver_id, driverName
FROM (
   SELECT race_result_details.id id, driver_id, driver.name as driverName, laptime
   FROM race_result_details 
   LEFT JOIN races ON races.id = race_result_details.race_id
   LEFT JOIN `drivers` `driver` ON `race_result_details`.`driver_id` = `driver`.`id` AND driver.name != ""
   WHERE (
    `race_result_details`.driver_id IS NOT NULL 
    AND races.track_length_id = 2
    AND `race_result_details`.lap_number > 0 
    AND `race_result_details`.laptime > 3449 
    AND `race_result_details`.`timestamp` > 1197426432
   )
   ORDER BY `race_result_details`.laptime ASC 
   limit 100
) f
GROUP BY driverName 
order by laptime ASC 
LIMIT 10;

What does it do

It selects the best ranked racers based on their minimal laptime, showing maximum once per driver name. It does so pretty well until I change the track_length_id to 2. Well, that should return faster, because we have less races with track_length_id = 2 right?

MariaDB > SELECT COUNT(*) FROM races WHERE races.track_length_id = 1;
+----------+
| COUNT(*) |
+----------+
|    33396 |
+----------+
1 row in set (0.038 sec)

MariaDB > SELECT COUNT(*) FROM races WHERE races.track_length_id = 2;
+----------+
| COUNT(*) |
+----------+
|     7919 |
+----------+
1 row in set (0.026 sec)

not really! Explain please.

id 1

+------+-------------+---------------------+--------+-------------------------------------+---------+---------+---------------------------------------------+--------+---------------------------------+
| id   | select_type | table               | type   | possible_keys                       | key     | key_len | ref                                         | rows   | Extra                           |
+------+-------------+---------------------+--------+-------------------------------------+---------+---------+---------------------------------------------+--------+---------------------------------+
|    1 | PRIMARY     | <derived2>          | ALL    | NULL                                | NULL    | NULL    | NULL                                        | 100    | Using temporary; Using filesort |
|    2 | DERIVED     | race_result_details | range  | race_id,timestamp,laptime,driver_id | laptime | 4       | NULL                                        | 598051 | Using where                     |
|    2 | DERIVED     | races               | eq_ref | PRIMARY,track_length_id             | PRIMARY | 4       | speeder_nette.race_result_details.race_id   | 1      | Using where                     |
|    2 | DERIVED     | driver              | eq_ref | PRIMARY                             | PRIMARY | 4       | speeder_nette.race_result_details.driver_id | 1      | Using where                     |
+------+-------------+---------------------+--------+-------------------------------------+---------+---------+---------------------------------------------+--------+---------------------------------+

vs id 2

+------+-------------+---------------------+--------+-------------------------------------+-----------------+---------+---------------------------------------------+------+----------------------------------------------+
| id   | select_type | table               | type   | possible_keys                       | key             | key_len | ref                                         | rows | Extra                                        |
+------+-------------+---------------------+--------+-------------------------------------+-----------------+---------+---------------------------------------------+------+----------------------------------------------+
|    1 | PRIMARY     | <derived2>          | ALL    | NULL                                | NULL            | NULL    | NULL                                        | 100  | Using temporary; Using filesort              |
|    2 | DERIVED     | races               | ref    | PRIMARY,track_length_id             | track_length_id | 4       | const                                       | 7919 | Using index; Using temporary; Using filesort |
|    2 | DERIVED     | race_result_details | ref    | race_id,timestamp,laptime,driver_id | race_id         | 5       | speeder_nette.races.id                      | 23   | Using where                                  |
|    2 | DERIVED     | driver              | eq_ref | PRIMARY                             | PRIMARY         | 4       | speeder_nette.race_result_details.driver_id | 1    | Using where                                  |
+------+-------------+---------------------+--------+-------------------------------------+-----------------+---------+---------------------------------------------+------+----------------------------------------------+

So, I kinda see that the planner picked up a way which seemed like an optimal one and failed. I had some class about database systems in university but it's not enough to understand this correctly.

I've been trying to switch up the indexes in race_result_details table (but that doesn't seem to be a problem in the first place), swap the order of WHERE clauses (does it really matter? I feel like I remember instances where it did help, but that's probably related only to multi column indexes, am I right?).

The difference is huge, 0.011s to 3.5s

Is there a good way to trick the planner to pick the way it did for the first query?

Edit:

Tables (trimmed to minimal example):

CREATE TABLE `drivers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,   
  `name` varchar(128) NOT NULL DEFAULT '',
  `username` varchar(128) NOT NULL DEFAULT '', 
  PRIMARY KEY (`id`),     
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `races` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',    
  `track_length_id` int(11) NOT NULL,    
  PRIMARY KEY (`id`),    
  KEY `track_length_id` (`track_length_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `race_result_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,    
  `race_id` int(11) DEFAULT NULL,   
  `race_group_id` int(11) DEFAULT NULL,
  `driver_id` int(11) DEFAULT NULL,     
  `lap_number` int(11) NOT NULL,      
  `laptime` int(11) NOT NULL COMMENT 'real lap time',      
  `timestamp` int(11) NOT NULL,
  PRIMARY KEY (`id`),     
  KEY `race_id` (`race_id`),
  KEY `timestamp` (`timestamp`),
  KEY `laptime` (`laptime`),
  KEY `driver_id` (`driver_id`),
  CONSTRAINT `race_result_details_ibfk_1` FOREIGN KEY (`race_id`) REFERENCES `races` (`id`),
  CONSTRAINT `race_result_details_ibfk_3` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON DELETE SET NULL,
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Changing LEFT JOIN to JOIN and deleting driver_id from the where didn't change anything at all.

Answer 1

So I did some last second googling after formuling the sentences which I wanted to ask and came up with an answer for one of them:

Is there a good way to trick the planner to pick the way it did for the first query?

Yes, there is! The index key used on the biggest table was "laptime" on the fast query. I am not sure why laptime (maybe it has the lowest number of values in it? Correct me if wrong please.)

It was just enough to add

FORCE INDEX (laptime)

just after FROM clause and it started to work lightning fast again. Yahoo! I keep this question here for future strugglers. I am still looking for somebody to answer more in depth response, which I will hopefuly get.