How to force an index on inner joined tables?

mysql inner-join

David · Feb 22, 2010 · Viewed 36.6k times · Source

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

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