SQL SELECT LIMIT does not affect count(*), etc

I was surprised to learn that LIMIT has no effect on aggregate functions like count(*). I would have expected that LIMIT would have stopped the query as soon as that number of records were found. Is this the expected behavior?

My objective was to quickly determine if any records existed that met a particular WHERE clause. I don’t really need to know how many total records there are, just that there is at least one (so there is no need to continue reading records once the first record has been found). Is there an efficient way to do this, other than using count(*)?

Hi @eric24

The count(*) function iterates all the results, just becase it has to count them. The LIMIT is applied to the final result. If you do something like

SELECT count(*) FROM V where name = 'foo' LIMIT 10

you will always obtain only one result, so the LIMIT 10 has no meaning.

Could you please give us a bit more context on the use case? I’m sure we can find a more efficient query

Thanks

Luigi

Hi @luigidellaquila

Here’s what I ended up doing:

SELECT @rid FROM V WHERE (...) LIMIT 1

This seems to be the most efficient way to quickly determine if a particular record exists (especially when the WHERE clause could potentially return many matching records). I’ve tested by doing EXPLAINs and the query does indeed stop as soon as one matching record has been found (as expected).

If you can think of a better way, please let me know.

I was just a bit surprised that LIMIT didn’t apply to an aggregate function. I haven’t gone back and tested it, but I feel like it does work that way on MSSQL, where I’ve used this approach before.