count performance

[gaurav.shetti]

I am running a query which is of the is form

select count(*) from doc_id where x=10 and b in (1,2,3,4 .... upto 50 entries) and query like abc

the count is taking too much time to return. it takes about 2000 seconds to return the count

If i remove the count and print docids it is done very fast. A binary index exists on doc_id.

I also tried
select count(doc_id) from doc_id where x=10 and b in (1,2,3,4 .... upto 50 entries) and query like abc

still the same old story.

Any suggestions to improve the performance.

There are about million documents that match the query and are satisfied by the above sql condition

[John]

We'd need more information to know for sure. Which indices do you have? When you say selecting records is fast, is that to select all, or just the first few?

[gaurav.shetti]

xdocid group_profile xdocid A B 01 doc_id

This is from the SYSINDEX table.

And I am actaully putting the print statement within sql
<sql> print </sql>

and breaking the loop after 15 records. So you can say i am outputting a few records.

[John]

It sounds as if you probably do not have indexes that can resolve the entire query, so it needs to read in all potentially matching rows to evaluate the entire where clause to see how many actually match.

You could either create appropriate indexes, or after you've selected 15 to display look at $indexcount or $rows.min and $rows.max to get an estimate of number of matches if you do not need a precise count.

[gaurav.shetti]

Can you explain the last line with an example

[mark]

<sql max=15 "select ...">
print
</sql>
$loop of about $indexcount matches.
or
<if $rows.min ge 0>
$loop of <if $rows.min ne $rows.max>at least </if>$rows.min matches.
</if>

http://www.thunderstone.com/site/vortex ... s_max.html
http://www.thunderstone.com/site/vortex ... count.html

[John]

<sql max=15 $sql>
<print>
</sql>
There are about $indexcount matches (between $rows.min and $rows.max)

The number in $indexcount should be the number of records that matched from the index, before the rest of the where clause is evaluated.

[gaurav.shetti]

In that case indexcount will be much larger than the actual count. Sometimes multiplied by 10 that huge, as we have many conditions in where clause