24

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
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
David
  • 16,246
  • 34
  • 103
  • 162
  • 3
    Could you please explain what you mean by "forcing" an index. Do you mean create it automatically (won't work) or do you mean forcing mysql to use it (see posts below). – AxelEckenberger Feb 22 '10 at 10:20

2 Answers2

35

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

Ηλίας
  • 2,560
  • 4
  • 30
  • 44
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.

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146