List:General Discussion« Previous MessageNext Message »
From:Jerry Preeper Date:October 14 1999 4:56pm
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.
>> >
>> 
>> Not to turn from student to teacher, but - that's assuming there's an index
>> on id.
>> 
>
>No, it's recognizing that there *might* be an index on id.  What you
>were proposing would either be something which couldn't be counted on to
>give a meaningful answer in all cases, or it would rule out some
>attractive optimizations available to the database engine.
>
>> 
>> 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?)
>> 


In this case here, id is my primary key in the jpnews table which doesn't
have any other indexes in it.  Ultimately the query will change to a left
join pulling info from a writer table, but I won't need the left join in
the count so that should help.  Giving results right now on query time
probably doesn't add much value since I'm just using a bunch of test data
and I just cleared most of it, but running the query returns 3 rows in set
(0.00 sec) with big text blobs in the story field (766, 562 and 454 word
stories).  

Jerry

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