How to use index in select statement?

Lets say in the employee table, I have created an index(idx_name) on the emp_name column of the table.

Do I need to explicitly specify the index name in select clause or it will automatically used to speed up queries.

If it is required to be specified in the select clause, What is the syntax for using index in select query ?

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

In general, the index will be used if the assumed cost of using the index, and then possibly having to perform further bookmark lookups is lower than the cost of just scanning the entire table.

If your query is of the form:

SELECT Name from Table where Name = 'Boris'

And 1 row out of 1000 has the name Boris, it will almost certainly be used. If everyone's name is Boris, it will probably resort to a table scan, since the index is unlikely to be a more efficient strategy to access the data.

If it's a wide table (lot's of columns) and you do:

SELECT * from Table where Name = 'Boris'

Then it may still choose to perform the table scan, if it's a reasonable assumption that it's going to take more time retrieving the other columns from the table than it will to just look up the name, or again, if it's likely to be retrieving a lot of rows anyway.