Will the query do indexed search or sequential search

Nagendran · New User Joined: 24 Jan 2007 Posts: 89 Location: USA

Hi,

I am having below query with the index developed on fields
COL1,COL2,COL3,COL4 and COL5.

The value of COL1=COL2=COL3=SPACES.

Select * from Tab1
where COL1>=' '
AND COL2>=' '
AND COL3>=' '
AND COL4>='1001'
AND COL5>='1002 '.

will the above query do indexed search or sequential search,

regards,
Nagendran.R

Pons · Posted: Mon Aug 27, 2007 11:01 pm

I hope it will use only the index search. Because there is no negative condition present in the query(NOT condition).

dbzTHEdinosauer · Posted: Mon Aug 27, 2007 11:18 pm

what Pons said is true, they are all Stage 1 predicates, but to find out you should always use EXPLAIN, since the optimizer makes its decisions based on your DB and its statistics.

But, why are you using SELECT *? That is not good SQL. The optimizer might just decide to do a full table scan, since you are going to access everything anyway.

Learn to use Explain.

dbzTHEdinosauer · Posted: Tue Aug 28, 2007 12:00 am

and, since you have no ORDER BY clause, there is no need to use the index to prohibit a sort.