thanks Fred! that's better. actully I took a field with one character but it's better to run SELECT '1' FROM....
as I know, if there is not ORDER BY in the query, mySQL doesn't need to check all records and retreives LIMIT number of them. I checked speed with limit and without limit, limit was faster. thanks for your help ----- Original Message ----- From: "Fred van Engen" <[EMAIL PROTECTED]> To: "Mojtaba Faridzad" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, July 22, 2003 10:52 AM Subject: Re: how to limit COUNT(*) > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
