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