I have a table called X and another called Y. There is a primary key on Y that is a foreign key on X. Now, my data is such that there will be many rows in X that will have the exact same foreign key value.
I want to know if MySQL will look up the Y table every time in select using a join even when the value is the same, or does it not have to search the new table a second time when I do the select?
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 theINDEXwithout creating the FK.) – Rick James Jul 7 '17 at 20:58