Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 2 of 2 total
Thread Sql with more than one field comparation
Thu, Jan 5 2006 4:24 AMPermanent Link

"Jose Eduardo Helminsky"
Tim

I have some tables with a lot of records (around 300,000) and the primary
key is composed by four fields.
I have made some tests and create another field called key with a
concatenation of these fields, the results is amazing fast.

For example:
select * from table where f1=1 and f2='' and f3='2005-12-15' and f4='04'

was changed to
select * from table where key='1;;2005-12-15;05'

I think it is normal because almost all records have f1=1 and f2=''

But the difference between speed is so huge, is it normal ? If so my
approach will be a solution to speed up the query. Any comments ?

Eduardo

Thu, Jan 5 2006 4:57 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Eduardo,

<< But the difference between speed is so huge, is it normal ? >>

Sure.  You're taking something that normally uses only one index scan for
the first column along with a record scan for the other columns and
converting into only one index scan.  That's a lot of I/O savings.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image