MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mojtaba Faridzad Date:July 22 2003 2:16pm
Subject:Re: how to limit COUNT(*)
View as plain text  
First of all, I don't wanna let the query run to the end and after that,
count the number of records. as I mentioned, some tables are huge and takes
a long time to run COUNT(*) query for all records.

About LIMIT, it doesn't effect on COUNT query. but for the second query (
retreiving a field ) I can use it and as you mentioned, I use LIMIT v1,v2.
this part of query that I tried to use COUNT is before retreiving the all
fields of the last page. it's just checking for the number of records, to
find the number of pages and show the last page.

about why I am not using auto_number or other indexes, because the table and
conditions are variable and on run-time.

----- Original Message ----- 
From: "Viorel Dragomir" <bc@stripped>
To: "Mojtaba Faridzad" <mfaridzad@stripped>;
<mysql@stripped>
Sent: Tuesday, July 22, 2003 9:50 AM
Subject: Re: how to limit COUNT(*)


> ----- Original Message -----
> From: "Mojtaba Faridzad" <mfaridzad@stripped>
> To: <mysql@stripped>
> Sent: Tuesday, July 22, 2003 4:40 PM
> Subject: Re: how to limit COUNT(*)
>
>
> > 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?
> >
>
> I dunno if I understood your problem. But if you want to *limit the
count()*
> try to put a condition after fetching the result.
> if( count_result > MAX_NO) then count_result = MAX_NO;
>
> If you want to paginate your result try use LIMIT v1, v2 [look in manual
for
> more information about SELECT & LIMIT]
>
> If you just want to view the last info rows try to order desc over an
> auto_increment field.
>
> good luck
>
> > thanks
> >
> > ----- 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
> >
>
>

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