45

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

JYelton
  • 35,664
  • 27
  • 132
  • 191
frequent
  • 27,643
  • 59
  • 181
  • 333
  • 4
    For what it's worth, USE INDEX doesn't force the named index to be used. It just means "don't consider using any *other* index." The index you name might be irrelevant to the query, in which case MySQL will still not use it. – Bill Karwin Jan 20 '14 at 22:46

4 Answers4

76

You missed the

FROM table

Correct SQL should be: 

SELECT art.firma FROM your_table USE INDEX (i_iln) WHERE ....
Community
  • 1
  • 1
Raptor
  • 53,206
  • 45
  • 230
  • 366
5
select * from table use index (idx);

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

hichris123
  • 10,145
  • 15
  • 56
  • 70
Markus Mikkolainen
  • 3,397
  • 18
  • 21
3

sometimes, with use index (index_name) optimizer might go for table scan, if you use hint force index, optimizer will be forced to use index, will go for table scan only if no ways left to get the rows with provided index.

SELECT art.firma FROM art FORCE INDEX (i_iln);

for more detail on hints USE INDEX and FORCE INDEX check this link

MikA
  • 5,184
  • 5
  • 33
  • 42
-1

Select Coloumn1,Coloumn2,Coloumn.... FROM TABLE_NAME USE INDEX(index_name) WHERE Coloumn="condition";

if you have correct index thn you dnt need to use index(). your query automic select correct index.If your query slow after using index thn recheck your index ,something wrong in index. thanks in advance.enter code here

kuldeep upadhyay
  • 42
  • 1
  • 8
  • Your answer would benefit from some editing towards readability, some formatting and some deleting of texts fragments which look like unused template placeholders. – Yunnosch May 29 '17 at 11:14
  • Welcome to SO. Please read this [how-to-answer](http://stackoverflow.com/help/how-to-answer) and follow the guideline there to provide quality answer. – thewaywewere May 29 '17 at 11:31