4

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?

  • 3
    Why are you wondering about this? Is it because you want to know how they implemented joins or are you concerned about future performance degradation's? If it is out of curiosity I believe recent implementations have various join methods, so the answer might depend. If it is the latter I would not consider it particular important, there are probably a lot of other things that makes more difference performance-wise. – Lennart Jan 10 '16 at 11:38
  • 1
    And in the worst case it will be a PK lookup for data which are probably already loaded in all available caches. – jkavalik Jan 10 '16 at 19:18
  • You have described the cost of having FOREIGN KEYs, namely that repeated lookup. (The benefit is the implicit INDEX, but one can create the INDEX without creating the FK.) – Rick James Jul 7 '17 at 20:58
1

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.

  • "Using join buffer" is MySQL's way to say it loaded the entire index (or something) into RAM for quicker lookup. – Rick James Jul 7 '17 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.