Clever Geek Handbook

How do I get a mysql query to use a specific index?

SELECT * FROM orders WITH (INDEX(idx));

When I made the request above, I got the error

mysql # 1064 - You have an error in your SQL syntax

I created an index below

create index idx on orders(date,status);

Can anyone tell me the correct syntax?

+3


source to share


1 answer


If the index is appropriate, it will be used without explicitly specifying it.

Given what you are using SELECT *

, I would not expect your index to be used (even if the INDEX hint had the correct syntax). The choice depends on the heuristic of the query optimizer.

The correct syntax is:



SELECT * FROM orders USE INDEX(idx);

Reference: Pointing Hints

Also note : 99 times out of 100, specifying an index hint should not be done. Let the optimizer do its job.

+1


source






More articles:


All Articles