CODEWITHSUNDEEP
sqldatabaseindexing
Vivek
Vivek

Reputation: 13238

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 ?

Upvotes: 137

Views: 617659

Answers (8)

Cory
Cory

Reputation: 81

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.

Upvotes: 4

Tnadev
Tnadev

Reputation: 10082

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

Upvotes: 38

Jason
Jason

Reputation: 1430

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.

Upvotes: 134

MD Sayem Ahmed
MD Sayem Ahmed

Reputation: 29166

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.

Upvotes: 7

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

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.

Upvotes: 15

niktrs
niktrs

Reputation: 10066

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

Upvotes: 9

Tudor Constantin
Tudor Constantin

Reputation: 26861

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

Upvotes: 44

Kinexus
Kinexus

Reputation: 12904

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.

Upvotes: 7

Related Questions