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

[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.

[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

[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