Counting the rows that match a
WHERE condition can be slow, and may return a very high and precise number that is useful to no one. Let’s see how to just check if a query returns more than N rows.
Consider the following query:
SELECT count(*) FROM product WHERE size < 100 AND price < 1000;
We could output the result to the user:
Your search has returned 15 results.
This message can be useful. But if the search returns 97,226 rows, the query could be slow, and the user will have no interest in knowing such an exact number. Instead, let’s just output:
Your search has returned more than 100 results.
Here’s how to do it:
SELECT count(*) FROM ( SELECT id FROM product WHERE size < 100 AND price < 1000 LIMIT 101 );
The nested query will read at most 101 rows, in which case our application can write “your search returned more than 100 rows”. This means that the query will be faster.
The outer query will simply count the rows found by the nested query.
Depending on the DBMS you use, you may need to use
FETCH FIRST 101 ROWS ONLY instead of