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