Index Use

If you have a three columns table with one index on 2nd Column. In the SELECT statement you retrived column 1, can index be used or not?

Questions by kumarnavnit31

Showing Answers 1 - 9 of 9 Answers

dbxplorer

  • Oct 10th, 2010
 

Yes, Index on second column can be used in SELECT query.
Ex: Table 'T1' having three columns 'Name','City', 'Country'.

Second column is having index on it. Now your query to select 'Name'

SELECT Name
FROM T1
WHERE
City='MUMBAI';

But remember indexed column should have unique values.

Thanks,
Ankur

  Was this answer useful?  Yes

Diptiman.B

  • Nov 12th, 2010
 

No the index will not be used unless you use the INDEXED column in WHERE clause.

Keeping the INDEXED column alone in the SELECT query will make the difference.

Create and test it yourself.

  Was this answer useful?  Yes

shivaranjani.p

  • Dec 20th, 2010
 

In this case, the index will not get used, since the query doesn't not have WHERE clause.

It doesn't matter whether you retrieve indexed column or Non indexed column, the thing which matters is the filtering condition, i.e., the column specified in the WHERE clause.

If the query has indexed column in the WHERE clause, then definitely the index associated with that indexed column will get used.


  Was this answer useful?  Yes

nirmal1in

  • Dec 25th, 2010
 

It is not guarantee that query will use index column2, unless we use column 2 in where clause by default.

Also even though we use column 2 in the where clause, if we use patern matching

say

WHERE empname like '%Raj%' 

will not use an index. 

Index will be used when we use for direct match.


  Was this answer useful?  Yes

Rakesh

  • Jul 22nd, 2011
 

Index is used for columns specified in WHERE clause and not in SELECT clause. So the answer is YES

  Was this answer useful?  Yes

geetha

  • Jul 30th, 2011
 

index will be used if the retrieval is based on an indexed column .if the where clause uses a column with index then the query will be executed based on that index.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions