SQL: Fast threshold counts with LIMIT

Performing COUNT(*) on large tables is slow. Sometimes you don’t need the exact number once results exceed a certain threshold.

For example, you may only need to display 100+ in the UI. Using a plain COUNT(*) would scan all matching rows.

The following query would be pretty slow when counting many rows because it has to scan all rows.

SELECT COUNT(*) FROM movies;

Limiting within a subquery

Use a subquery with LIMIT to cap the scan early. You’ll get the exact number up to 100 otherwise you can show 100+.

SELECT COUNT(*) FROM (SELECT 1 FROM movies LIMIT 101) AS more_than_100;

Note

Use LIMIT n+1 to detect > n and filters such as WHERE conditions should be put inside the subquery.

If you only care whether the result contains more than 100 rows, use EXISTS.

SELECT EXISTS (SELECT 1 FROM movies OFFSET 100 LIMIT 1);