CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
Taichi
Taichi

Reputation: 2597

MySQL: does indices needed on both fields of table that are joined?

When I execute a SQL like this;

SELECT * 
  FROM table_foo 
  JOIN table_bar
    ON table_foo.foo_id = table_bar.bar_id

do I need an index just on table_foo.foo_id ?

Or does MySQL uses both indices on table_foo.foo_id and table_bar.bar_id ?

The result of EXPLAIN is like this.

enter image description here

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

There are multiple possible execution plans for this query:

SELECT f.*, b.* 
FROM table_foo f JOIN
     table_bar b
     ON f.foo_id = b.bar_id;

Here are some examples:

  1. The one you want to avoid (presumably) is a nested loop join that loops through one table -- row by row -- and then for each row loops through the second one.
  2. Scan foo and look up each value in bar, using an index on table_bar(bar_id). From the row id in the bar index, get the associated columns for each matching row.
  3. Scan bar and look up each value in foo, using an index on table_foo(foo_id). From the row id in the foo index, get the associated columns for each matching row.
  4. Scan both indexes using a merge join and look up the associated rows in each of the tables.

This leave out other options such as hash join which would not normally use indexes.

So, either or both indexes might be used, depending on which algorithms the optimizer implements. That is, one index is often going to be good enough to get the performance you want. But, you give the optimizer more options if you have an index on both tables.

Upvotes: 1

Related Questions