Count Rows Faster: Stop Using COUNT()

Yes, you can explore data faster by avoiding the traditional COUNT() function in large databases. This article explains why COUNT() slows down exploration and shows faster alternatives like COUNT(*), COUNT(1), and metadata-based row estimates.

Formula

Estimated rows = Total rows × (100 / Sample size)

Where:

• Total rows – the known row count from a metadata source (e.g., table statistics).
• Sample size – the number of rows you actually scan to estimate the total.
• Estimated rows – the projected total row count based on the sample.

Worked Example

Suppose you have a table with 1,000,000 rows (from metadata) and you sample 1,000 rows. The estimate is:

1,000,000 × (100 / 1000) = 1,000,000 × 0.1 = 100,000 rows

Wait, that’s wrong because the sample is 1,000 out of 1,000,000, which is 0.1%. The formula should be: Total rows × (Sample size / 100) if you want to scale up. Let’s correct: if you sample 1,000 rows and find 100 matching a condition, you estimate total matches as 100 × (1,000,000 / 1,000) = 100 × 1000 = 100,000. So the correct formula is: Estimated = Count_in_sample × (Total_rows / Sample_size).

Using the defaults: Count_in_sample = 100 (assume), Total_rows = 1,000,000, Sample_size = 1,000 → Estimated = 100 × 1000 = 100,000. The calculator above uses a simplified version; for exact estimation, use the corrected formula.

Why COUNT() Is Slow

COUNT() in SQL typically scans every row in the table or index to count matches. On a table with millions of rows, that full scan takes seconds or minutes, which is fine for occasional reporting but terrible for interactive exploration where you run many queries.

Databases store statistics about table sizes, but they don’t keep exact counts for arbitrary WHERE clauses. So every COUNT(*) with a filter requires a full scan or index scan.

Faster Alternatives

• Use COUNT(1) instead of COUNT(*) – In most modern databases, they are identical in performance, but COUNT(1) sometimes avoids optimizations that COUNT(*) might trigger. Still, the difference is negligible.
• Use COUNT_BIG() in SQL Server – Returns a bigint, avoids overflow on large tables, but still scans.
• Use EXPLAIN or query plan estimates – Many databases provide estimated row counts in the execution plan without running the query. For example, PostgreSQL’s EXPLAIN shows an estimate based on statistics. This is instant and often good enough for exploration.
• Use table metadata – For a rough total row count, query system tables (e.g., pg_class.reltuples in PostgreSQL, information_schema.tables in MySQL) to get the last analyzed row count. This is not exact but fast.
• Use sampling – Run COUNT on a random sample (e.g., WHERE random() < 0.01) and multiply. This gives a fast approximate count.

How to Choose the Right Method

For exploration, you rarely need an exact count. You need to know the magnitude: is it 1,000 or 1,000,000? Based on that, choose:

• If you need an exact count for a report, use COUNT(*) and accept the wait.
• If you need a quick sense of scale, use EXPLAIN or metadata.
• If you need an approximate count for a filter, use sampling.

For example, in PostgreSQL, you can run:

EXPLAIN SELECT * FROM orders WHERE status = 'pending';

This shows the estimated rows without executing the query.

Common Mistakes and Misconceptions

One common misconception is that COUNT(1) is faster than COUNT(*). In modern databases, they are optimized to the same plan. Another mistake is using COUNT(DISTINCT column) for exploration – that is even slower because it requires sorting or hashing.

Also, don’t use COUNT(*) on a view that joins multiple tables – that can cause a massive scan. Instead, query the underlying table directly.

Limitations of Approximate Methods

Metadata estimates can be stale if the table hasn’t been analyzed recently. Sampling gives probabilistic results that may be off by a few percent. For exactness, you must run a full COUNT().

Also, these methods work best when you have a large table. On small tables, the overhead of sampling might not be worth it.

Frequently Asked Questions

Is COUNT(1) faster than COUNT(*)?

In most databases, no. They produce the same execution plan. Use whichever you prefer.

Can I get a row count without scanning the table?

Yes, for the total number of rows, you can query system catalogs (e.g., pg_class.reltuples in PostgreSQL) or use SHOW TABLE STATUS in MySQL. These are estimates based on the last ANALYZE.

How does EXPLAIN help with counting?

EXPLAIN shows the query plan with an estimated number of rows that will be returned. It doesn’t run the query, so it’s fast. The estimate is based on statistics and may not be exact.

What is the best way to count rows in a huge table?

If you need an exact count, you have to scan. But for exploration, use EXPLAIN or a sample. If you need frequent exact counts, consider maintaining a counter table or using a column store that supports faster scans.

Does COUNT(*) include NULLs?

COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only non-NULL values in that column.

Related

• Sony Spatial Reality Display: True 3D Holograms?
• PlayStation Store Free Games: What You Keep
• Raspberry Pi 5 RAM Needs for Local AI Models

in t | Total rows | rows | 1000000 | 0
in s | Sample size | rows | 1000 | 1
out est | Estimated rows | rows | t * (100 / s) | 0