OR between Regular and Metamorph searches SLOW

skalyanaraman · Post by **skalyanaraman** » Wed Mar 03, 2004 2:42 pm

Hi,
We are running Texis version from OCT 28 2003.
We are running a count of all records with
"FIELD1 > '1000' and FIELD1 < '2000' or FIELD2 like 'test auto insurance @0'"
This select runs for more than 20 mins.
But, if I do FIELD2 like 'test auto insurance @0'
I get the count in less than minute,
and FIELD1 > '1000' and FIELD1 < '2000' comes back in less than a 5 seconds.

Could you help us with this? We have regular index on FIELD1 and metamorph inverted on FIELD2.

TIA!!

Post by **mark** » Wed Mar 03, 2004 2:51 pm

Does it work better if you parenthesize?
(FIELD1 > '1000' and FIELD1 < '2000') or FIELD2 like 'test auto insurance @0'

skalyanaraman · Post by **skalyanaraman** » Wed Mar 03, 2004 3:24 pm

That did not help. it probably took the same time.

Post by **John** » Thu Mar 04, 2004 11:52 am

The difference is probably due to the fact that for the individual parts of the query it can just look at the index for the count, whereas with the OR it is looking at the records after processing the index to count. You may be able to select a field from the table, retrieve one row, and look at indexcount to get an fairly accurate count.