Re: select count(*) and limit

On 5/18/06 8:22 AM, "Verena Ruff" <lists(at)triosolutions(dot)at> wrote:

> Sean Davis schrieb:
>>
>> On 5/18/06 6:51 AM, "Verena Ruff" <lists(at)triosolutions(dot)at> wrote:
>>
>>
>>> Hi,
>>>
>>> sometimes I have queries with a LIMIT statement. Now I'd like to present
>>> the user the returned records and inform him how many records there are
>>> if there was no LIMIT statement. Is it possible to get all neccessary
>>> information with one query?
>>> This works:
>>> SELECT * FROM table LIMIT 20
>>> SELECT count(*) FROM table
>>> But is it possible to have one query returning both, the records and the
>>> count?
>>>
>>
>> Verena
>>
>> I think the answer is "no", not when using "LIMIT". However, For the count
>> part, a trick to speed things up is to use the output from EXPLAIN to
>> approximate the number of rows. If the table has been vacuumed on a regular
>> basis, the results are often pretty close to those returned by count(*).
> My chosen example was to simple, sorry for that. The real query isn't
> just from one table, it contains a few joins, so I guess this trick
> won't work here.

It should still work just fine, again with the caveat that it is an
approximation and depends on the statistics available. Try comparing the
output a few times for your count(*) and using EXPLAIN.

Sean