How to force an index on inner joined tables?

Question

How do I force indexes on a query similar to this. I need to force an index on foo and bar individually.

SELECT foo.*, bar.*
FROM foo
INNER JOIN bar ON foo.rel_id = bar.rel_id
WHERE foo.status = 1
  AND bar.status = 1

Answer 1

If you have a compound index on
foo (status, rel_id)
and one on
bar (status, rel_id)
there should be no need to force using them.

Answer 2

The obvious thing would be to create a covering index on rel_id and status of both tables to satisfy the join and where requirement.

What have you tried so-far?

edit

You provide index hints but the crux of everyone answering seems to be that you shouldn't have to do that.

From MySQL 4.0.9 on, you can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.

Answer 3

Hi,

Assuming index a exists on foo and index b on bar:

SELECT foo.*, bar.*
FROM foo FORCE INDEX (a)
INNER JOIN bar FORCE INDEX (b) ON foo.rel_id = bar.rel_id
WHERE foo.status = 1
  AND bar.status = 1

would force index selection on MySql