Does creating a Foreign Key makes INNER JOIN faster than 2 index on the field joining?

Question

I work with tables that have a lot of data and I'm trying to reduce the request time. There are just a few foreign keys on the tables and a lot of indexes.

If I add foreign keys instead of indexes will querying be faster?

key and index are synonyms - If you add an fk for which there is no appropriate index mysql will create one,,You should review the manual — P.Salmon, Feb 17 '23 at 09:29
There is no difference does the index is created separately and explicitly, or it is created implicitly during FK creation. FK itself is consistency rule and does not effect the selection performance. — Akina, Feb 17 '23 at 10:22
[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [mre] [ask] [Help] [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) — philipxy, Feb 17 '23 at 11:20

score 0 · Accepted Answer · answered Feb 17 '23 at 22:47

(Your question is ambiguous; I will answer it in a couple of ways.)

SELECT ... FROM a JOIN b ON a.x = b.x will benefit with INDEX(x) on either table. You could either explicitly create INDEX(x) or let FOREIGN KEY ... create it for you. The end result is the same -- the JOIN will run a lot faster.

If, by "2 index on the field joining", you really mean "a 'composite' index when 2 columns are used for joining", then

SELECT ...
    FROM a
    JOIN b
      ON a.x = b.x AND a.y = b.y

Together with the composite (multi-column) INDEX(x,y) will perform well.

Note: This will not be as useful: INDEX(x), INDEX(y).

A 3rd example:

SELECT ...
    FROM a 
    JOIN b  ON a.x = b.x
    WHERE b.z = 123

The optimal indexes are likely to be

b:  INDEX(z)
a:  INDEX(x)

That is, handling one table in the WHERE clause before considering the JOIN is usually best.

More: Index Cookbook

Does creating a Foreign Key makes INNER JOIN faster than 2 index on the field joining?

1 Answers1