> and < searches running slowly

Mr. Bigglesworth · Post by **Mr. Bigglesworth** » Thu Jul 24, 2003 12:37 pm

We have a couple of fields with regular indexes on them:

"create index index01 on table1(field01)"
"create index index02 on table1(field02)"

when I run a search like this with tsql I get a count returned quickly:

select count(MYID) on table1 where field01 > '77787676' and field02 < '81076437'

476 hits in total.

However, when I try to write out the results, it takes five minutes to do so:

select MYID on table1 where field01 > '77787676' and field02 < '81076437'

If I leave out the AND bit and run each term individually, IDs stream on the screen lighting-quick. Can you think of why the AND would slow things down so much here?

We're using Solaris version 4.00.1019027849. Thanks.

Mr. Bigglesworth · Post by **Mr. Bigglesworth** » Thu Jul 24, 2003 12:52 pm

...naturally, I meant to type "select count from" up there.

Mr. Bigglesworth · Post by **Mr. Bigglesworth** » Thu Jul 24, 2003 5:09 pm

It takes 10-15 seconds, then a single MYID (which is a unique indexed field, for what it's worth) appears.

After that, we're looking at a minute or two before the other MYIDs show up, but when they do, it's quick. Then, another 30 second delay before the command prompt returns-- like it's still looking for more hits, but doesn't find any?