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

mysql sql indexing hint
frequent picture frequent · Jul 31, 2012 · Viewed 78k times · Source

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

Raptor picture Raptor · Jul 31, 2012

You missed the

FROM table

Correct SQL should be: 

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

Related questions

SQL: set existing column as Primary Key in MySQL

I have a database with 3 columns: id, name, somethingelse This table has no index set and i am getting "No index defined!" in phpmyadmin id is a 7digit alphanumeric value, unique to each row. I want to set Drugid to …

Read More
Two single-column indexes vs one two-column index in MySQL?

I'm faced with the following and I'm not sure what's best practice. Consider the following table (which will get large): id PK | giver_id FK | recipient_id FK | date I'm using InnoDB and from what I understand, it creates indices …

Read More
Does index on Varchar make performance difference?

Does index on a varchar column make the query run slower? I can make that be int. and I don't need to do the LIKE % comparison.

Read More