Still Using COUNT(*) to Count Rows? Discover Faster & More Efficient Alternatives!

Still Using COUNT(*) to Count Rows? | Abhishek Shakya

When working with SQL databases, many developers rely on COUNT(*) to count the number of rows in a table. While this method is straightforward, it may not always be the most efficient—especially for large datasets.

If you’re still using COUNT(*), it's time to explore faster and more optimized alternatives that can improve query performance and reduce database load. In this article, we’ll explore why COUNT(*) can be slow, its alternatives, and when to use them.

Why Can COUNT(*) Be Slow?

The COUNT(*) function counts all rows in a table, including those with NULL values. On small tables, this isn't a problem. However, when dealing with millions or billions of rows, COUNT(*) can become a performance bottleneck.

Here’s why:
✅ Full Table Scan — If there’s no index, COUNT(*) may scan the entire table.
✅ Concurrency Issues – Running COUNT(*) frequently can affect database performance.
✅ Locking Overhead – On large tables, counting rows may cause locking, slowing down other queries.

Now, let’s explore better alternatives!

🚀 Faster Alternatives to COUNT(*)

1. Use Indexed COUNT(ColumnName) Instead of COUNT(*)

If your table has an index, counting a specific column (instead of COUNT(*)) can be faster.

✅ Example:

SELECT COUNT(id) FROM Employees;

🔹 Why is it faster?

• If id is indexed, the database can count rows more efficiently.
• Some databases optimize COUNT(column_name) using indexes.

❌ When NOT to use:

• If the column has NULL values, COUNT(column_name) won’t count those rows.

2. Use COUNT(1) Instead of COUNT(*)

Some believe COUNT(1) is faster than COUNT(*), but in most modern databases, they perform the same.

✅ Example:

SELECT COUNT(1) FROM Employees;

🔹 Why use it?

• In older databases, COUNT(1) could be marginally faster than COUNT(*).
• In most modern databases (MySQL, PostgreSQL, SQL Server, Oracle), there is no difference.

3. Use Approximate COUNT for Faster Results (Big Data Optimization)

For big data tables, exact row counts may not always be necessary. Some databases offer approximate count functions, which are faster.

✅ MySQL Approximate Count:

SHOW TABLE STATUS LIKE 'Employees';

🔹 Faster but may not be 100% accurate.

✅ PostgreSQL Approximate Count:

SELECT reltuples::bigint FROM pg_class WHERE relname = 'employees';

🔹 Returns an estimated row count instead of scanning all rows.

4. Store and Maintain Row Count in a Separate Table

For huge datasets, you can maintain a row count separately instead of querying it repeatedly.

✅ Example: Using a trigger to maintain row count

CREATE TABLE row_count (table_name VARCHAR(50), row_count INT);

CREATE OR REPLACE FUNCTION update_row_count() RETURNS TRIGGER AS $$
BEGIN
    UPDATE row_count SET row_count = row_count + 1 WHERE table_name = 'Employees';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

🔹 Best for high-traffic databases where counting all rows frequently is expensive.

5. Use Indexed Views for Faster Counting

An indexed view stores precomputed results, making counting operations significantly faster.

✅ Example:

CREATE VIEW EmployeeCount AS  
SELECT COUNT(*) AS total FROM Employees;

🔹 Querying this view is faster than running COUNT(*) each time.

🚀 Conclusion: Best Practices for Counting Rows Efficiently

If you’re working with large databases, switching from COUNT(*) to optimized methods can improve performance and reduce load. Choose the right strategy based on your use case and database type. 🚀