According to MySQL 5.7 documentation (current as of now):
8.8.2 EXPLAIN Output Format
The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.
Emphasis mine. That is, I guess that MySQL isn't intelligent enough to remember that "it already looked some key".
MariaDB uses several Block-Based Join Algorithms in current versions:
- Block Nested Loop (BNL) join
- Block Nested Loop Hash (BNLH) join
- Block Index join known as Batch Key Access (BKA) join
- Block Index Hash join known as Batch Key Access Hash (BKAH) join
The "Batch Key Access" methods seems to take some advantage ... the key part of the docs is:
Yet BKA join allows to optimize the order in which the records are fetched.
FOREIGN KEYs
, namely that repeated lookup. (The benefit is the implicitINDEX
, but one can create theINDEX
without creating the FK.) – Rick James Jul 7 '17 at 20:58