count query

Post Reply
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

count query

Post by gaurav.shetti »

i have a small clarification.

can we restrict the count query to count only 500 records which match the condition. If not, is there any technique which helps me to achieve the above case.

my code looks something like this


<sql ROW max=10 select statement>
<if $loop eq 0>
<sql count query>
</if>

show results on front end

</sql>


This sql count query takes time when the results to be returned are something like 1,00,0000 records.

Can you suggest me techniques to counter this and save that time.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

count query

Post by John »

If there is an index for the select statement you can look at the $indexcount variable, or $rows.min and $rows.max.
John Turnbull
Thunderstone Software
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

count query

Post by gaurav.shetti »

But i have many clauses in the where condition. Would still indexcount give me the proper value
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

count query

Post by gaurav.shetti »

one of the condition is something like ...
where Prod in (a,b,c, ..... some 100 ids)

will indexcount still be the proper variable to be used?
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

count query

Post by gaurav.shetti »

is there anything like setting the max of the countquery to some number
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

count query

Post by John »

No, if $indexcount works then use that. The MAX only works when selecting records, as your other thread about $loop asks.
John Turnbull
Thunderstone Software
gaurav.shetti
Posts: 119
Joined: Fri Feb 27, 2009 9:09 am

count query

Post by gaurav.shetti »

agreed, $indexcount does give me accurate number when i am doing a simple select query. When i used conditions like date posted between a certain range in the where clause, indexcount contains number much greater than the actual results returned.

So my question is, is there a way or any setting which ensures that only top 500 records are searched for. I am planning to improve the performance of my application as when the results retrieved are as great as 1 million, the time taken is about 100 - 120 seconds.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

count query

Post by John »

You would need to select the records with a max=500 to see if there are more or less than 500 records.

Also if indexcount is not accurate it may suggest that using different indexes may be appropriate, as it is not able to fully use the index for your query.
John Turnbull
Thunderstone Software
Post Reply