MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Fred van Engen Date:July 22 2003 3:45pm
Subject:Re: how to limit COUNT(*)
View as plain text  
Hi,

On Tue, Jul 22, 2003 at 11:30:47AM -0400, Mojtaba Faridzad wrote:
> 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.
> 

Correct, but you might want to check that MySQL uses an index to find
those records. If it needs to do a full table scan to find the 10001st
matching record at the end of the table, you've gained very little. That
was the point I tried to make earlier.

You can even use an ORDER BY combined with a LIMIT if you make sure that
the optimizer uses the same index for the ORDER BY and the evaluation of
your conditions. If it doesn't, it will first select all records, sort
them and return the first 10001.


Use EXPLAIN to find out what it does with your set of 'mycondition'. You
may want to limit your user's ability to influence 'mycondition' to
things that optimize well.

Also, optimization depends on the size and content of your tables, so
you want to try this with real-world data.

If the optimizer chooses the wrong index, you can tell it from which
indexes to choose by using 'USE (myindex)'. Use this e.g. to force it to
use the same index as for an ORDER BY. If it decides to use NO index, I
think you have a problem.


Regards,

Fred.


> ----- 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
> >

-- 
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
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