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 ?

asked Jul 6, 2011 at 8:48 13k 19 19 gold badges 98 98 silver badges 133 133 bronze badges

8 Answers 8

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.

219k 134 134 gold badges 711 711 silver badges 1.6k 1.6k bronze badges answered Jul 6, 2011 at 22:31 1,390 1 1 gold badge 8 8 silver badges 2 2 bronze badges

This post does not answer the question. The answer is, in short: you don't have to specify index in query. It is used (or not) automagically. You can force it, however. More details (when and why do this) in other posts below.

Commented Apr 28, 2014 at 11:05

This post answers the most relevant part "What is the syntax for using index in select query ?". It does not explicitly say "it is" or "it is not" required. In my use case, there is an index, but for some reason it is not used. Adding WITH(INDEX(..)) next to the table name solves the problem for me!

Commented Nov 27, 2020 at 9:37

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

answered Jul 6, 2011 at 8:52 Tudor Constantin Tudor Constantin 26.8k 7 7 gold badges 52 52 silver badges 73 73 bronze badges

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?

answered Mar 18, 2015 at 6:59 9,917 7 7 gold badges 67 67 silver badges 72 72 bronze badges

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.

answered Jul 6, 2011 at 8:53 Damien_The_Unbeliever Damien_The_Unbeliever 238k 28 28 gold badges 354 354 silver badges 460 460 bronze badges

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.

Depending on your RDBMS you can force the use of an index, although it is not recommended unless you know what you are doing.

In general you should index columns that you use in table join's and where statements

answered Jul 6, 2011 at 8:52 10.1k 1 1 gold badge 31 31 silver badges 31 31 bronze badges

By using the column that the index is applied to within your conditions, it will be included automatically. You do not have to use it, but it will speed up queries when it is used.

SELECT * FROM TABLE WHERE attribute = 'value'

Will use the appropriate index.

answered Jul 6, 2011 at 8:51 12.9k 6 6 gold badges 42 42 silver badges 63 63 bronze badges

Generally, when you create an index on a table, database will automatically use that index while searching for data in that table. You don't need to do anything about that.

However, in MSSQL, you can specify an index hint which can specify that a particular index should be used to execute this query. More information about this can be found here.

Index hint is also seems to be available for MySQL. Thanks to Tudor Constantine.

answered Jul 6, 2011 at 8:53 MD Sayem Ahmed MD Sayem Ahmed 29.1k 27 27 gold badges 114 114 silver badges 181 181 bronze badges

The index hint is only available for Microsoft Dynamics database servers. For traditional SQL Server, the filters you define in your 'Where' clause should persuade the engine to use any relevant indices. Provided the engine's execution plan can efficiently identify how to read the information (whether a full table scan or an indexed scan) - it must compare the two before executing the statement proper, as part of its built-in performance optimiser.

However, you can force the optimiser to scan by using something like

 Select * From [yourtable] With (Index(0)) Where .

Or to seek a particular index by using something like

 Select * From [yourtable] With (Index(1)) Where .

The choice is yours. Look at the table's index properties in the object panel to get an idea of which index you want to use. It ought to match your filter(s).

For best results, list the filters which would return the fewest results first. I don't know if I'm right in saying, but it seems like the query filters are sequential; if you get your sequence right, the optimiser shouldn't have to do it for you by comparing all the combinations, or at least not begin the comparison with the more expensive queries.