sql - How to hint the index to use in a MySQL select query?

Question

I have a MySQL query (running MySQL 5.0.88), which I'm trying to speed up. The underlying table has multiple indices and for the query in question, the wrong index is used (i_active - 16.000 rows, vs. i_iln - 7 rows).

I'm not very experienced with MySQL but read there is a use index hint, which can force mySQL to use a certain index. I'm trying it like this:

 SELECT art.firma USE INDEX (i_iln)
 ...

but this produces a MySQL error.

Question:
Can anyone tell me what I'm doing wrong? (Except running 5.0.88, which I can't change.)

question from:https://stackoverflow.com/questions/11731822/how-to-hint-the-index-to-use-in-a-mysql-select-query

Answer 1

You missed the

FROM table

Correct SQL should be:

SELECT art.firma FROM your_table USE INDEX (i_iln) WHERE ....