List:General Discussion« Previous MessageNext Message »
From:Jay J Date:October 14 1999 4:58am
Subject:Re: using limit and also getting total count
View as plain text  
> > ----- Original Message -----
> > From: Jerry Preeper <preeper@stripped>
> > To: <mysql@stripped>
> > Sent: Wednesday, October 13, 1999 4:10 PM
> > Subject: using limit and also getting total count
> >
> > > I have the following sql query I am using and was wondering if there
is
> > any
> > > way of getting a total count of matching items in the database but
> > limiting
> > > the results to 3 or whatever number of items.
> > >
> > > I have shortened the query somewhat for irrelevant stuff:
> > > $sql_query = "select id,date,headline,type,story from jpnews ";
> > > $sql_query .= "where type=1 and status=1 "
> > > $sql_query .= "order by id desc limit 3";
> > > $sql_result = $dbh->Query($sql_query);
> > > $howmany = $sql_result->numrows;
> > >
[snip]
> > >
> > > Do I need to do two separate queries (one for the count plus the one
> > above)
> > > or is there some efficient way to get this into the same query?
> > >
> > > Thanks
> > > Jerry
> >
> > No, you're forced to run a seperate count. (I'd love to be wrong!)
> >
> > On this topic though, I was wondering ... since this query is SORTed
> > with a WHERE clause, isn't MySQL forced to examine every record
> > matching the WHERE, _then_ sort (at least once)? So doesn't it know
> > the total count anyway? If so, why not return it on queries where
> > this is the case?
>
> Not necessarily.  Your hypothesis makes assumptions about the choices
> the optimizer will make, and these assumptions aren't guaranteed to be
> valid.  What if, for example, the optimizer decided after examining the
> distributions that it would be less expensive to walk backwards through
> an index on id and stop after it had found three rows which had the
> desired type and status.  Or there might be an index on type + status +
> id.
>
> --
> Bob Kline

Thanks Bob.

Not to turn from student to teacher, but - that's assuming there's an index
on id.

Which I didn't, when I first looked at it... hence the silly question.

Your explanation certainly helps in my understanding of the magic of indices
and optimizers. Can I ask something else though?

I must admit I've read over "Chapter 10: Getting maximum performance from
MySQL" dozens of times, and performed my own benchmarks with different
settings ... but for the most part I'm taking the recommendations at face
value. In other words, the source code and some of the concepts are terribly
humbling. :-| "I get it... but I don't.. sorta kinda."

Question: Has there been any discussion about cacheing the answers to
queries? If I understand correctly something like the 'key_buffer' is shared
amongst the threads, keys .. but not answers. (right?)

So in the example above, maybe he is forced to run a count(*) which returns
6, but if none of the tables have changed in the interim then next time the
cached answer is returned? (I realize this isn't a new concept)

-Jay




Thread
using limit and also getting total countJerry Preeper13 Oct
  • Re: using limit and also getting total countJay J14 Oct
    • Re: using limit and also getting total countBob Kline14 Oct
      • Re: using limit and also getting total countJay J14 Oct
        • Re: using limit and also getting total countBob Kline14 Oct
          • Re: using limit and also getting total countJerry Preeper14 Oct
          • Re: using limit and also getting total countRyan Dickinson18 Oct
        • Re: using limit and also getting total countMichael Widenius14 Oct
      • Re: using limit and also getting total count [cacheing query answers]Jay J16 Oct
    • Re: using limit and also getting total countMichael Widenius14 Oct
  • Re: using limit and also getting total countGerald Clark15 Oct