Hi, On Tue, Jul 22, 2003 at 09:40:31AM -0400, Mojtaba Faridzad wrote: > for example: > > SELECT COUNT(*) as numfound FROM mytable WHERE mycondition; > > in this query, mytable and mycondition are variable and on run time, they > are changed. I use this query to jump to the last page of a grid form. > sometimes the query may have more than million records and I want to give a > warning to the user to specify a condition to limit the number of records. I > would like to count the records upto 10000 (for example) and if the records > are more than this, stop counting and ask user to change the condition. to > solve this problem I did something like this: > > SELECT one_field FROM mytable WHERE mycondition LIMIT 10001; > > if the number of records of this query is equal to 10001, then I show the > warning message. but this query is not as fast of COUNT query. is there any > way to limit the first query? >
My best attempt would be: SELECT 1 FROM mytable WHERE mycondition LIMIT 10001; Then get the result count without getting the actual results :( What you gain here is that MySQL will use just an index file if it can. By querying for 'one_field', it would use the data table if 'one_field' is not part of the index that is used for evaluating 'mycondition'. Are you sure that this query helps? Depending on 'mycondition', it might not be possible to use an index anyway. All records would need to be checked, even if the number of matches were less than 10001. If your query is guaranteed to use an index, you add ORDER BY ... DESC combined with a LIMIT to go to the last page of your form. You won't know the actual count to display then. Regards, Fred. > ----- Original Message ----- > From: "gerald_clark" <[EMAIL PROTECTED]> > To: "Mojtaba Faridzad" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, July 22, 2003 9:15 AM > Subject: Re: how to limit COUNT(*) > > > > Perhaps you could post some examples of what you have tried. > > I don't understand what you are asking. > > > > Mojtaba Faridzad wrote: > > > > >Hi, > > > > > >I guess there is no way to limit COUNT(*). Is that right? We cannot use > the > > >result of COUNT in WHERE condition or LIMIT doesn't help. In this case so > > >far I have retrieved a field and used LIMIT. Is there a better way to > > >control it? > > > > > >Thanks > > > > > > > > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
