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

Question

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

When I fired above query I got the error

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

I have created index as below

create index idx on orders(date,status);

Can anybody tell me the correct syntax?

Answer 1

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

Given you are using SELECT * I would not expect your index to be used (even if the INDEX hint had the correct syntax). The choice is down to the query optimiser's heuristics.

The correct syntax is:

SELECT * FROM orders USE INDEX(idx);

Ref: Index Hints

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