When working with real-world databases, developers often issue SELECT COUNT(*) queries. This pattern is commonly called using COUNT(*) in production. It is often used just to check whether any rows exist for a condition. This approach works, but it is rarely the most efficient way to answer a yes-or-no question about data. It is especially costly when COUNT(*) in production runs frequently on large tables.

COUNT(*) in Production

In most cases, it does not matter whether there is one row or one million rows. What matters is only whether any row exists. For this requirement, using SELECT COUNT(*) is usually the most expensive option. The database engine must scan and count every qualifying row before it can return a result. On large tables with millions of rows, this can consume significant CPU time, I/O, and locking. Under high-concurrency workloads, that extra work can quickly become a real bottleneck.

You May Also Like: Complex SQL Queries for projects and interviews

Queries should be written so they stop as soon as the first matching row is found, unless the total row count is truly required. This pattern is sometimes called an early-exit or short-circuit strategy. Most SQL dialects provide simple, direct syntax to support it.

Writing a COUNT(*) in Production Query

Bad Practice: Using COUNT(*) in Production for Existence Checks

MOVE ZEROS TO HOST-VARIABLE1.
EXEC SQL
 SELECT COUNT(*)
 INTO : HOST-VARIABLE1
 FROM TABLE1
 WHERE COLUMN1 = :HOST-VARIABLE-X
 AND COLUMN2 = :HOST-VARIABLE-Y
END-EXEC
IF HOST-VARIABLE1 > ZERO
SET ROWS-FOUND TO TRUE
END-IF

Good Practice: Efficient COUNT(*) in Production Alternatives

Why COUNT(*) Can Be Problematic in Production

The core issue is that COUNT(*) is an aggregate function. It must visit every row that satisfies the WHERE clause before it can return a number. That makes heavy use of COUNT(*) in production unnecessarily expensive for simple existence checks. Even when the predicate columns are indexed, the optimizer must follow the index to every matching entry. On very large tables this workload grows linearly with the data size. In contrast, an existence check using FETCH FIRST 1 ROW ONLY stops as soon as it finds one match. Its cost stays roughly constant, which makes it a much better choice than relying on COUNT(*) in production when you only need a yes-or-no answer.

In high-traffic OLTP systems, unnecessary COUNT(*) queries can inflate CPU utilization. They can also increase the duration of row-level locks. Longer locks raise contention and slow down concurrent writes. Replacing these queries with targeted existence checks is a low-risk, high-reward optimization.

When COUNT(*) in Production Is Appropriate

COUNT(*) is the right tool when the actual number of rows is needed. Examples include reporting queries, pagination to calculate total pages, or data-quality checks that must report exact counts. The problem appears only when it is used instead of a simple existence check.

Practical Alternatives to COUNT(*) in Production

Several patterns are commonly used to check for the existence of a row without performing a full count:

• SELECT 1 … FETCH FIRST 1 ROW ONLY — Stops after the first matching row. This is the pattern shown in the good-practice example above. It is portable across DB2, Oracle (using FETCH FIRST or ROWNUM), SQL Server (TOP 1), and MySQL/PostgreSQL (LIMIT 1).
• EXISTS subquery — Many optimizers treat EXISTS as a short-circuit predicate and stop scanning as soon as a row is found. For example: IF EXISTS (SELECT 1 FROM TABLE1 WHERE COLUMN1 = :VAR) ….
• COUNT of a specific indexed column — When nullability is not a concern and the column is indexed, COUNT(indexed_column) can be faster than COUNT(*). In this case, the optimizer may satisfy the count entirely from the index without touching the base table. However, for a pure existence check, FETCH FIRST 1 ROW ONLY is still preferred.

COUNT(*) in Production Performance Considerations

No matter which existence-check pattern you choose, make sure the WHERE clause columns are covered by an appropriate index. Without an index, even a FETCH FIRST 1 ROW ONLY query may need a full table scan in the worst case. An index on the predicate columns lets the engine navigate directly to qualifying rows. This makes the early-exit strategy as efficient as possible.

Conclusion: Use COUNT(*) in Production Carefully

Reserve SELECT COUNT(*) for situations where a precise row count is genuinely required. When the goal is simply to confirm that at least one row satisfies a condition, rewrite the query to return after the first hit instead of using COUNT(*) in production. This small change can meaningfully reduce resource consumption on busy systems. It also communicates the true intent of the query more clearly to anyone reading the code later, and it discourages overuse of COUNT(*) in production for basic existence checks.

MOVE ZEROS TO HOST-VARIABLE1
SELECT 1
INTO :HOST-VARIABLE1
FROM TABLE1
WHERE COLUMN1 = :HOST-VARIABLE-X
 AND COLUMN2 = :HOST-VARIABLE-Y
FETCH FIRST 1 ROW ONLY
EVALUATE SQLCODE …‥
IF SQLCODE = 0
 SET ROWS-FOUND TO TRUE
END-IF

The good-practice example uses SELECT 1 instead of COUNT(*) and adds FETCH FIRST 1 ROW ONLY (DB2/z syntax; equivalent to LIMIT 1 in many other databases). The database engine stops processing as soon as it finds a single qualifying row. As a result, the work performed is proportional to the cost of locating one row rather than all rows.

• Click for SQL tuning refresher guide