mysql> explain extended select l.* from VEHICLE v inner join LOCATION l on v.id=l.vehicle_id where v.id=3 and l.creation_date = (select max(l2.creation_date) from VEHICLE v2 inner join LOCATION l2 on
v2.id = l2.vehicle_id where v2.id = 3);
+----+-------------+-------+-------+---------------------------------------+--------------------+---------+-------------+--------+----------+-------------+
| id | select_type | table | type  | possible_keys                         | key                | key_len | ref         | rows   | filtered | Extra       |
+----+-------------+-------+-------+---------------------------------------+--------------------+---------+-------------+--------+----------+-------------+
|  1 | PRIMARY     | v     | const | PRIMARY                               | PRIMARY            | 8       | const       |      1 |   100.00 | Using index |
|  1 | PRIMARY     | l     | ref   | FK9FF58FB532802E38,CREATIONDATE,VILID | VILID              | 17      | const,const |      4 |   100.00 | Using where |
|  2 | SUBQUERY    | v2    | const | PRIMARY                               | PRIMARY            | 8       |             |      1 |   100.00 | Using index |
|  2 | SUBQUERY    | l2    | ref   | FK9FF58FB532802E38                    | FK9FF58FB532802E38 | 8       |             | 526591 |   100.00 |             |
+----+-------------+-------+-------+---------------------------------------+--------------------+---------+-------------+--------+----------+-------------+
4 rows in set, 1 warning (5.51 sec)