List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 14 1999 2:46pm
Subject:Re: using limit and also getting total count
View as plain text  
>>>>> "Jay" == Jay J <3pound@stripped> writes:

Jay> ----- Original Message -----
Jay> From: Jerry Preeper <preeper@stripped>
Jay> To: <mysql@stripped>
Jay> Sent: Wednesday, October 13, 1999 4:10 PM
Jay> Subject: using limit and also getting total count


>> I have the following sql query I am using and was wondering if there is
Jay> any
>> way of getting a total count of matching items in the database but
Jay> 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);

<cut>

Jay> No, you're forced to run a seperate count. (I'd love to be wrong!)

Jay> On this topic though, I was wondering ... since this query is SORTed with a
Jay> WHERE clause, isn't MySQL forced to examine every record matching the WHERE,
Jay> _then_ sort (at least once)? So doesn't it know the total count anyway? If
Jay> so, why not return it on queries where this is the case?

Jay> Just wondering,

Hi!

If MySQL would do a sort on the full result set, this would be true.

Now there is many other scenarios:

- If the ORDER BY is an index, MySQL will not do a sort.
- If the query uses many tables, then MySQL may only sort the first
  table and limit will stop when it has found 'LIMIT' matched rows
  when joining.  In this case there is no way to know how many rows
  there would be in the final result.

If possible, the best way is to modify the interface to ask for limit
+1 query and only present 'limit' rows + a note that there was more
rows that matched the query.

Regards,
Monty
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