MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mojtaba Faridzad Date:July 22 2003 3:30pm
Subject:Re: how to limit COUNT(*)
View as plain text  
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" <fred.van.engen@stripped>
To: "Mojtaba Faridzad" <mfaridzad@stripped>
Cc: <mysql@stripped>
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" <gerald_clark@stripped>
> > To: "Mojtaba Faridzad" <mfaridzad@stripped>
> > Cc: <mysql@stripped>
> > 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/mysql?unsub=1
> > >
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
> -- 
> Fred van Engen                              XB Networks B.V.
> email: fred.van.engen@stripped                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/mysql?unsub=1
>

Thread
Odd thingDave Christensen19 Jun
  • Re: Odd thingDon Read19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDave Christensen19 Jun
  • RE: Odd thingMatthew Smith19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDallas Dickey19 Jun
  • selecting PRIMARY KEY when there is no unique valueMojtaba Faridzad19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valueBruce Feist19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valuePaul DuBois19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valueDon Read19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valuePeterWR19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueMichael Conlen19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueMojtaba Faridzad19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueKen Menzel19 Jun
  • how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)gerald_clark22 Jul
  • Re: how to limit COUNT(*)Jerry22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
    • Re: how to limit COUNT(*)Fred van Engen22 Jul
  • Re: how to limit COUNT(*)Viorel Dragomir22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)Yves Goergen22 Jul
    • RE: how to limit COUNT(*)Mike Brum22 Jul
      • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)gerald_clark22 Jul
    • Re: how to limit COUNT(*)Keith C. Ivey22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
    • Re: how to limit COUNT(*)Fred van Engen22 Jul
RE: Odd thingDallas Dickey19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDave Christensen19 Jun
RE: selecting PRIMARY KEY when there is no unique valueMike Hillyer19 Jun
RE: Odd thingDave Christensen19 Jun
RE: Odd thingDave Christensen19 Jun
RE: Odd thingDave Christensen19 Jun
  • Re: Odd thinggerald_clark19 Jun
RE: Odd thingDave Christensen19 Jun