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.)

Answer 1

You missed the

FROM table 

Correct SQL should be:

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

Answer 2

select * from table use index (idx); 

http://dev.mysql.com/doc/refman/5.0/en/index-hints.html