COUNT() #

COUNT() is the aggregate function that appears in almost every application — for pagination, statistics dashboards, notification badges, or reports. Syntactically it looks simple and harmless. But beneath the surface, COUNT() is one of the most common silent bottlenecks in production systems: it forces the database to read far more data than you’d imagine, can’t be answered from an index alone in most cases, and if placed on a frequently called endpoint, its impact multiplies with every request. This article covers the behavioral differences of each COUNT() variant, why it’s expensive on large tables, and five concrete strategies to replace or optimize its use in production.

Three COUNT() Variants and Their Behavioral Differences #

Before discussing performance problems, it’s important to understand that COUNT() isn’t one function — there are three variants with different semantics and behaviors, and choosing the wrong variant can give both wrong results and slower performance.

COUNT(*) — Counts All Rows Including NULLs #

COUNT(*) counts the number of rows returned by the query, without checking the contents of any column. This is the most efficient variant because the database doesn’t need to evaluate any specific column values.

-- Count all rows in the table, including those with NULL values in any column
SELECT COUNT(*) FROM orders;

-- Count rows matching a WHERE condition
SELECT COUNT(*) FROM orders WHERE status = 'paid';

-- Count rows in each group
SELECT user_id, COUNT(*) as total_orders
FROM orders
GROUP BY user_id;

COUNT(column) — Counts Rows with Non-NULL Values #

COUNT(column) counts rows where that column isn’t NULL. This is slower than COUNT(*) because the database must check each column’s value.

-- Count orders that have a value in shipping_at (already shipped)
SELECT COUNT(shipping_at) FROM orders;
-- Differs from COUNT(*) if any rows have shipping_at = NULL

-- Behavior comparison:
-- Rows: 1000 total, 400 already have shipping_at, 600 still NULL
SELECT COUNT(*)          FROM orders;  -- → 1000
SELECT COUNT(shipping_at) FROM orders;  -- → 400

COUNT(DISTINCT column) — Counts Unique Values #

COUNT(DISTINCT column) is the most expensive because the database must collect all values, deduplicate them, then count. On large tables without optimization, this almost always requires a temporary table.

-- Count how many different users have orders
SELECT COUNT(DISTINCT user_id) FROM orders;

-- EXPLAIN for COUNT(DISTINCT) on a large table:
-- Extra: "Using temporary"  ← the sign of an expensive operation
-- If the result doesn't need to be exact, consider alternative approaches

Relative cost comparison of the three COUNT() variants:
──────────────────────────────────────────────────────────────
  COUNT(*)            → Cheapest
                        No need to check column values
                        In MySQL InnoDB: still needs an index scan
                        In PostgreSQL: needs a visibility check (MVCC)

  COUNT(column)       → More expensive than COUNT(*)
                        Must check NULL/non-NULL for every row
                        Almost no reason to choose this
                        except explicitly excluding NULLs

  COUNT(DISTINCT col) → Most expensive
                        Collect all values → deduplicate → count
                        Often shows "Using temporary"
                        Consider alternatives for tables > 100k rows
──────────────────────────────────────────────────────────────

Why COUNT() Is Expensive on Large Tables #

Behavior in MySQL InnoDB: No “Magic Number” #

There’s often a misconception that the database stores the row count somewhere readable directly — a sort of row_count = 5,000,000 metadata. In MySQL InnoDB, this doesn’t exist. Every time COUNT(*) executes, the database must actually count:

-- A query that looks simple
SELECT COUNT(*) FROM orders;

-- What happens in MySQL InnoDB:
-- 1. Choose the smallest index (not the main table)
-- 2. Scan that entire index from start to finish
-- 3. Count every valid (non-deleted) entry
-- 4. Return the result

-- Its EXPLAIN:
EXPLAIN SELECT COUNT(*) FROM orders;
-- +------+-------------+--------+-------+------+------+------+-------------+
-- | type | key         | rows   | Extra                                    |
-- +------+-------------+--------+-------+------+------+------+-------------+
-- | index| idx_status  | 5000000| Using index                              |
-- +------+-------------+--------+-------+------+------+------+-------------+
-- "Using index" means scanning the entire index (not the table)
-- But 5 million entries still must be read one by one

MySQL MyISAM stores the row count in metadata, so COUNT(*) without WHERE can be O(1). But InnoDB — used by almost all modern systems because of its transaction support — doesn’t have this shortcut.

Behavior in PostgreSQL: MVCC Complicates Everything #

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which lets multiple transactions see different data snapshots simultaneously. The consequence: there’s no single “valid row count” number that applies to all transactions.

-- In PostgreSQL, COUNT(*) must:
-- 1. Scan every row (heap scan or index scan)
-- 2. Check every row's visibility based on transaction IDs
--    (is this row visible to the current transaction?)
-- 3. Only count if visible

-- On tables frequently UPDATEd or DELETEd,
-- there are many "dead tuples" that must be skipped
-- → Regular VACUUM is very important for COUNT() performance

-- Check dead tuples in a table:
SELECT relname, n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- A high dead_pct → slower COUNT(), needs VACUUM

COUNT() with WHERE: Index Scans Are Still Expensive #

Adding a WHERE condition helps the database limit the rows read, but it still must scan every row matching the condition:

-- A very common pagination query
SELECT COUNT(*) FROM users WHERE status = 'active' AND deleted_at IS NULL;

-- With an index on (status, deleted_at):
-- → type: ref, key: idx_users_status_deleted
-- → rows: 850000  (estimated rows scanned)
-- → Extra: Using index

-- 850,000 index entries must be read and counted.
-- If this endpoint is called 500x/minute:
-- → 425 million index entries read per minute just for COUNT()
-- → Not a small load

The Hidden Problem: COUNT() in Pagination #

The most common and most problematic pattern is combining COUNT(*) with a pagination query to display “Page 1 of 47”:

-- Two queries run every time a user opens a list page
SELECT * FROM products WHERE category_id = 5 ORDER BY created_at DESC LIMIT 20 OFFSET 0;
SELECT COUNT(*) FROM products WHERE category_id = 5;  -- ← this is the expensive one

What’s not realized: this COUNT() query recalculates from scratch on every request, even though the number of products in category 5 barely changes within a minute. If 10,000 users open the category page simultaneously, the database runs 10,000 COUNT()s, each scanning hundreds of thousands of rows — for almost identical results.

The impact of COUNT() on high-traffic pagination endpoints:
──────────────────────────────────────────────────────────────
  Table products: 2 million rows
  Index (category_id, created_at): exists
  category_id = 5: 180,000 products

  Every page request:
    → SELECT data: scans 20 rows (index + LIMIT) → fast
    → SELECT COUNT(*): scans 180,000 rows → slow

  At 1,000 requests/minute:
    → 180,000,000 index entries read per minute just for COUNT()
    → Database CPU driven up
    → Other queries slow down too
──────────────────────────────────────────────────────────────

Five Strategies for Replacing Expensive COUNT() #

Strategy 1: LIMIT+1 — Eliminate the Total Count Need #

What’s most often needed in pagination isn’t “how many total pages” but only “is there a next page?”. For this, LIMIT+1 is enough and far more efficient.

-- ANTI-PATTERN: count the total then determine whether there's a next page
SELECT COUNT(*) FROM products WHERE category_id = 5;  -- expensive
SELECT * FROM products WHERE category_id = 5 ORDER BY id LIMIT 20 OFFSET 0;

-- CORRECT: fetch one extra row, check in the application layer
SELECT * FROM products WHERE category_id = 5 ORDER BY id LIMIT 21;
-- If the result >= 21 rows → there's a next page, display only 20
-- If the result < 21 rows → no next page

Implementation in Go:

func GetProducts(categoryID int, limit int) (*ProductPage, error) {
    // Request one more than displayed
    rows, err := db.Query(`
        SELECT id, name, price, created_at
        FROM products
        WHERE category_id = ?
        ORDER BY created_at DESC
        LIMIT ?
    `, categoryID, limit+1)

    var products []Product
    for rows.Next() {
        var p Product
        rows.Scan(&p.ID, &p.Name, &p.Price, &p.CreatedAt)
        products = append(products, p)
    }

    hasNextPage := len(products) > limit
    if hasNextPage {
        products = products[:limit]  // trim back to the original limit
    }

    return &ProductPage{
        Products:    products,
        HasNextPage: hasNextPage,
        // No TotalCount — not needed
    }, nil
}

This pattern fits both cursor-based and simple offset-based pagination. The result: no COUNT() at all.

Strategy 2: Precomputed Counter Tables #

For counters read frequently but changing slowly — product counts per category, active user counts, orders per day — store the results in a counter table and update incrementally.

-- Create a dedicated counter table
CREATE TABLE category_stats (
    category_id  BIGINT UNSIGNED NOT NULL,
    product_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                 ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (category_id)
);

-- Update the counter every time a product enters a category
INSERT INTO products (name, category_id, price) VALUES (?, ?, ?);
-- Then:
INSERT INTO category_stats (category_id, product_count)
VALUES (?, 1)
ON DUPLICATE KEY UPDATE product_count = product_count + 1;

-- Update the counter when a product is removed from a category
UPDATE category_stats
SET product_count = GREATEST(0, product_count - 1)
WHERE category_id = ?;

-- ANTI-PATTERN: recalculate on every request
SELECT COUNT(*) FROM products WHERE category_id = 5;  -- scans hundreds of thousands of rows

-- CORRECT: read from the counter table — O(1)
SELECT product_count FROM category_stats WHERE category_id = 5;

Counter tables updated inside the same transaction as the main operation can cause lock contention on very active category tables. For systems with thousands of writes per second to one counter, consider batch update strategies or Redis counters.

Strategy 3: Approximate Count — Accurate Enough Estimates #

For many use cases — especially UI displays like “around 1.2 million articles” — full precision isn’t needed. A sufficiently accurate estimate can be obtained without any scan.

-- PostgreSQL: read the estimate from query planner statistics
SELECT reltuples::BIGINT AS estimated_count
FROM pg_class
WHERE relname = 'orders';
-- → Returns results in microseconds
-- → Accuracy usually within 5-10% of the real value
-- → Updated automatically by ANALYZE (periodic or manual)

-- MySQL: read the estimate from information_schema
SELECT TABLE_ROWS AS estimated_count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'orders';
-- → A rough estimate, accuracy varies
-- → Updated when ANALYZE TABLE is run

When an approximate count is enough:

Use cases suited to estimates:
  ✓ "Showing about X results for this search"
  ✓ Sidebar counter badges ("~1.2k articles")
  ✓ Internal monitoring dashboards
  ✓ Admin statistics not needing full precision

Use cases needing precision:
  ✗ Billing based on item counts
  ✗ Financial reports
  ✗ SLA compliance (must be exactly N items)
  ✗ Data integrity validation

Strategy 4: Cache COUNT() with Redis #

For endpoints needing relatively accurate numbers but not real-time, cache the COUNT() result in Redis with a reasonable TTL.

func GetActiveUserCount(ctx context.Context) (int64, error) {
    cacheKey := "stats:active_users_count"

    // Try the cache first
    val, err := rdb.Get(ctx, cacheKey).Int64()
    if err == nil {
        return val, nil  // cache hit
    }

    // Cache miss → count from the database
    var count int64
    err = db.QueryRowContext(ctx,
        "SELECT COUNT(*) FROM users WHERE status = 'active' AND deleted_at IS NULL",
    ).Scan(&count)
    if err != nil {
        return 0, err
    }

    // Store in cache with a 5-minute TTL
    rdb.Set(ctx, cacheKey, count, 5*time.Minute)

    return count, nil
}

// Invalidate the cache when a user's status changes
func UpdateUserStatus(ctx context.Context, userID int64, status string) error {
    _, err := db.ExecContext(ctx,
        "UPDATE users SET status = ? WHERE id = ?", status, userID)
    if err != nil {
        return err
    }

    // Remove the cache so the next read gets the latest value
    rdb.Del(ctx, "stats:active_users_count")
    return nil
}

This strategy fits counters that don’t change too often. A 5-minute TTL means the displayed number is at most 5 minutes stale — for dashboard statistics, this is almost always acceptable.

Strategy 5: Event-Driven Counters #

For systems already using message queues or event streaming, counters can be updated asynchronously based on events, not inside the main transaction.

Event-driven counter architecture:

flowchart TD
    subgraph Register["When a new user registers:"]
        direction TB
        App["Application"] -->|"INSERT users"| DB_Users["Users Table"]
        App -->|"publish event user.created"| Queue["Queue/Kafka/Redis Pub-Sub"]
        Queue --> Worker["Counter Service"]
        Worker -->|"UPDATE stats SET user_count = user_count + 1"| DB_Stats["Stats Table"]
    end

    subgraph Read["Read Path"]
        direction TB
        Client["Read via API: GET /stats/users"] -->|"read from the stats table (O(1))"| DB_Stats
    end

Advantages: ✓ The main transaction isn’t burdened with counter updates ✓ The counter service can retry on failure ✓ No lock contention on the main table ✓ Scales independently

Disadvantages: ✗ Eventual consistency — a gap between the event and the updated counter ✗ More architecturally complex ✗ Needs a reconciliation mechanism to correct drift

Comparing the Five Strategies #

The COUNT() strategy decision matrix:
──────────────────────────────────────────────────────────────────────
  Strategy              │ Precision │ Speed    │ Complexity │ Use Case
──────────────────────────────────────────────────────────────────────
  Direct COUNT(*)       │ Exact     │ Slow     │ Very easy  │ Small tables,
                        │           │          │            │ admin reports
  LIMIT+1               │ N/A       │ Fast     │ Easy       │ Only "next?"
  Precomputed counter   │ Exact     │ O(1)     │ Medium     │ Stable counters
  Approximate count     │ ~95%      │ O(1)     │ Easy       │ UI displays
  Cached COUNT()        │ ~exact    │ Fast     │ Medium     │ Medium traffic
  Event-driven counter  │ ~exact    │ O(1)     │ High       │ High traffic
──────────────────────────────────────────────────────────────────────

Decision tree:

flowchart TD
    Q1{"Must the number be fully precise?"}
    Q2{"Need to know 'is there a next page'?"}
    Q3{"How often does it change?"}

    A1["Approximate count (pg_class / information_schema)"]
    A2["LIMIT+1 (no COUNT needed at all)"]
    A3["Precomputed counter table"]
    A4["Cache COUNT() with Redis"]
    A5["Event-driven counter"]

    Q1 -->|"No"| A1
    Q1 -->|"Yes"| Q2

    Q2 -->|"Only that"| A2
    Q2 -->|"Need a total number"| Q3

    Q3 -->|"Rarely changes"| A3
    Q3 -->|"Fairly often"| A4
    Q3 -->|"Often + high traffic"| A5

When Direct COUNT() Is Still Appropriate #

COUNT() isn’t a function to avoid entirely — there are contexts where using it directly against the database is still appropriate and justified:

Direct COUNT() is still appropriate for:
──────────────────────────────────────────────────────────────
  ✓ Small tables (< 50,000 rows)
    → Fast scans, no performance problems

  ✓ Rarely run admin queries
    → Monthly reports, audits, one-time analyses
    → Not a hot path, taking a few seconds is fine

  ✓ Background jobs / cron
    → Not competing with user requests
    → Can run during off-peak hours

  ✓ Data integrity checks
    → Must be precise, estimates aren't acceptable
    → Low frequency

Direct COUNT() is NOT appropriate for:
──────────────────────────────────────────────────────────────
  ✗ Public pagination endpoints (called thousands of times per minute)
  ✗ Real-time dashboards refreshed constantly
  ✗ UI counter badges (notifications, unread messages)
  ✗ Million-row tables queried via APIs
  ✗ Queries inside loops or N+1 patterns

Anti-Patterns to Avoid #

-- ✗ Anti-pattern 1: COUNT(*) on every pagination request
-- Every time a user opens a list page, this query executes:
SELECT COUNT(*) FROM articles WHERE status = 'published';
-- ✓ Solution: LIMIT+1 for "next page", or a 60-second TTL cache

-- ✗ Anti-pattern 2: COUNT(DISTINCT col) on a hot path
SELECT COUNT(DISTINCT user_id) FROM page_views WHERE date = CURDATE();
-- → "Using temporary" on every request, very expensive
-- ✓ Solution: increment a Redis counter on each new page view,
--   read from Redis (O(1)), flush to the database periodically

-- ✗ Anti-pattern 3: COUNT() in subqueries executed repeatedly
SELECT *,
    (SELECT COUNT(*) FROM comments WHERE post_id = p.id) AS comment_count
FROM posts
WHERE status = 'published';
-- → COUNT() executed once for every posts row (an N+1!)
-- ✓ Solution: store comment_count in the posts table and update incrementally

-- ✗ Anti-pattern 4: COUNT() for existence checks
IF (SELECT COUNT(*) FROM users WHERE email = ?) > 0 THEN ...
-- → Counts every matching row, when only "exists or not" is needed
-- ✓ Solution: use EXISTS() — stops as soon as one row is found
IF EXISTS(SELECT 1 FROM users WHERE email = ?) THEN ...

-- ✗ Anti-pattern 5: COUNT(*) without an index on the WHERE column
SELECT COUNT(*) FROM events WHERE event_type = 'login' AND user_id = ?;
-- If no index on (user_id, event_type): full table scan
-- ✓ Solution: make sure the right index exists, or use a counter table

EXISTS vs COUNT for Existence Checks #

One very common anti-pattern is using COUNT() just to know whether a row exists or not. EXISTS() is far more efficient for this because it stops exactly at the first found row.

-- ANTI-PATTERN: COUNT() for an existence check
SELECT COUNT(*) FROM orders WHERE user_id = 42 AND status = 'pending';
-- The database scans every matching row and counts them all
-- When only "exists or not" is needed

-- CORRECT: EXISTS() stops at the first row
SELECT EXISTS(
    SELECT 1 FROM orders WHERE user_id = 42 AND status = 'pending'
);
-- → Find one row → immediately return TRUE, don't scan the rest

-- In Go:
var exists bool
db.QueryRowContext(ctx,
    "SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = ? AND status = 'pending')",
    userID,
).Scan(&exists)

-- Performance comparison for a user with 10,000 pending orders:
-- COUNT(*): scans 10,000 rows, counts all → slow
-- EXISTS():  stops at the first row → almost instant

COUNT() Usage Checklist #

BEFORE WRITING SELECT COUNT():
  □ Is this on a hot path / frequently called endpoint?
     If yes → consider alternatives first
  □ Is only "exists or not" needed?
     If yes → use EXISTS(), not COUNT()
  □ Is only "is there a next page" needed?
     If yes → use LIMIT+1
  □ Can the number be an estimate (not fully precise)?
     If yes → use an approximate count from pg_class / information_schema
  □ Does this counter change fairly infrequently?
     If yes → consider a precomputed counter table

IF COUNT() IS TRULY NEEDED:
  □ Does an index cover the columns in the WHERE clause?
  □ Verified with EXPLAIN (no full table scan)?
  □ Can the result be cached? What's a reasonable TTL?
  □ Will this scale? Is a counter table better long-term?

Summary #

• COUNT(*) has no shortcut in InnoDB — the database must actually read and count every valid row, even though the result feels like “one simple number”. There’s no O(1) row count metadata.
• COUNT(DISTINCT col) is the most expensive — it requires deduplication that almost always needs a temporary table. Use it only when distinct precision is truly needed.
• Use EXISTS() for existence checks, not COUNT() — EXISTS() stops at the first found row; COUNT() scans every matching row. On large tables the difference can be thousands of times faster.
• LIMIT+1 eliminates the total count need for pagination — if the UI only needs a “Next” button, you don’t need to know the total page count. Request one extra row, check in the application layer.
• Precomputed counter tables are the best solution for stable counters — incremental updates when data changes, O(1) reads whenever needed. Far more scalable than COUNT() per request.
• Approximate counts are enough for many UI use cases — pg_class.reltuples in PostgreSQL and information_schema.TABLE_ROWS in MySQL return estimates in microseconds. For “about X articles”, this is more than enough.
• Cache COUNT() results when truly needed — a 1-5 minute TTL is usually acceptable for statistics. Invalidate the cache on significant data changes.
• Avoid COUNT() on hot paths without caching — an endpoint called thousands of times per minute with a COUNT() inside can be the single cause of continuously rising database CPU.

← Previous: Composite Index   Next: Pagination →