How to use index in select statement?

If you want to test the index to see if it works, here is the syntax:

SELECT *
FROM Table WITH(INDEX(Index_Name))

The WITH statement will force the index to be used.


Good question,

Usually the DB engine should automatically select the index to use based on query execution plans it builds. However, there are some pretty rare cases when you want to force the DB to use a specific index.

To be able to answer your specific question you have to specify the DB you are using.

For MySQL, you want to read the Index Hint Syntax documentation on how to do this


How to use index in select statement?
this way:

   SELECT * FROM table1 USE INDEX (col1_index,col2_index)
    WHERE col1=1 AND col2=2 AND col3=3;


SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;


SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

And many more ways check this

Do I need to explicitly specify?

  • No, no Need to specify explicitly.
  • DB engine should automatically select the index to use based on query execution plans it builds from @Tudor Constantin answer.
  • The optimiser will judge if the use of your index will make your query run faster, and if it is, it will use the index. from @niktrl answer