List:General Discussion« Previous MessageNext Message »
From:Jay J Date:October 14 1999 12:34am
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;
>
> Naturally, $howmany just gives me 3 on a query I tested that returns 6
> without the limit.  I want the total count so I can display a more tab at
> the end of everything with the number of additional matching records and a
> link to another program that runs a different query when retrieving those
> items.  I also want to keep the limit since the stories are all in the
> database as text blobs and there is no sense returning a lot of data that
> I'm just not going to use in this query.
>
> 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?

Just wondering,

-Jay J



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