COUNT(*) vs COUNT(column) in MySQL: Key Differences, Performance & Real Examples

Introduction

Counting rows is one of the most common operations in SQL. Whether you're building dashboards, generating reports, implementing pagination, or displaying analytics, you've probably used the COUNT() function many times.

However, many developers are confused about the difference between the following:

• COUNT(*)

• COUNT(column_name)

Some believe it COUNT(*) is always slower because it counts every column, while others think COUNT(column) is faster. These are common misconceptions.

In this article, you'll learn how both functions actually work, how they handle NULL values, their performance differences, and when to use each one in real-world applications.

What Is COUNT() in MySQL?

The COUNT() function returns the number of rows based on the expression provided.

The two most commonly used forms are:

COUNT(*)

and

COUNT(column_name)

Although they look similar, they don't always return the same result.

Sample Table

Consider the following employees table.

There are 5 rows in total.

Understanding COUNT(*)

COUNT(*) counts every row in the table.

SELECT COUNT(*)
FROM employees;

Output:

5

It doesn't matter whether any column contains NULL.

As long as the row exists, it is counted.

Think of COUNT(*) as asking:

"How many rows are stored in this table?"

Understanding COUNT(column)

Now let's count the email column.

SELECT COUNT(email)
FROM employees;

Output:

3

Why?

Because MySQL ignores NULL values.

The two rows where email is NULL are not counted.

Another Example

Count the salary column.

SELECT COUNT(salary)
FROM employees;

Output:

4

One employee has a NULL salary, so that row is excluded from the count.

Visual Comparison

This is the biggest difference between the two functions.

Real-World Example 1: Total Users

Suppose you want to display:

Total Registered Users

The correct query is:

SELECT COUNT(*)
FROM users;

Even if some users haven't filled in their profile information, every user should be counted.

Real-World Example 2: Users Who Added a Phone Number

Suppose the phone_number column is optional.

SELECT COUNT(phone_number)
FROM users;

This returns only users who actually have a phone number.

Exactly what you need.

Real-World Example 3: Products With Images

SELECT COUNT(image_url)
FROM products;

If some products don't have images yet, they won't be included.

This is useful for finding how many products have completed information.

Does COUNT(*) Count Every Column?

No.

This is one of the biggest myths in MySQL.

Many developers assume:

COUNT(*) checks every column in every row.

That is not true.

The * simply means:

Count every row.

It does not read every column individually.

Which One Is Faster?

This is another common interview question.

In modern versions of MySQL using the InnoDB storage engine, the performance difference between COUNT(*) and COUNT(column) is usually negligible when both queries require scanning the same rows.

However:

• COUNT(*) is generally the preferred way to count all rows.

• COUNT(column) may require checking whether the column value is NULL.

• If the column is indexed, MySQL's optimizer may choose an efficient execution plan depending on the query.

Instead of worrying about COUNT(*) vs COUNT(column), focus on:

• Proper indexing

• Efficient WHERE clauses

• Avoiding unnecessary table scans

These factors have a much larger impact on performance.

COUNT(*) with WHERE Clause

You can combine COUNT(*) with conditions.

SELECT COUNT(*)
FROM orders
WHERE status = 'Completed';

This returns only completed orders.

COUNT(column) with WHERE Clause

SELECT COUNT(email)
FROM users
WHERE country = 'India';

This counts users from India whose email is not NULL.

COUNT(DISTINCT column)

Sometimes you want unique values instead of total values.

Example:

SELECT COUNT(DISTINCT city)
FROM customers;

If customers belong to only five different cities, the result will be:

5

Duplicate cities are counted only once.

Common Mistakes

Mistake 1

Using:

SELECT COUNT(email)
FROM users;

to count total users.

This is incorrect if some users don't have an email address.

Mistake 2

Assuming COUNT(*) is slower.

Modern MySQL optimizes COUNT(*) very efficiently.

Mistake 3

Ignoring NULL values.

Always remember:

COUNT(column) ignores NULLs.

Best Practices

Follow these guidelines:

• Use COUNT(*) when counting all rows.

• Use COUNT(column) only when you want to ignore NULL values.

• Use COUNT(DISTINCT column) for unique values.

• Add indexes to columns frequently used in filtering.

• Use EXPLAIN to analyze performance on large datasets.

Interview Questions

Q1. What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row, while COUNT(column) counts only rows where the specified column is not NULL.

Q2. Does COUNT(*) count NULL values?

Yes. Since it counts rows rather than column values, NULL values do not affect the result.

Q3. Is COUNT(column) faster?

Not necessarily. In most real-world scenarios with modern MySQL versions, the performance difference is minimal. Query design and indexing are far more important.

Summary

Conclusion

Although COUNT(*) and COUNT(column) appear similar, they solve different problems.

Use COUNT(*) whenever you need the total number of rows in a table. It counts every record regardless of whether any column contains NULL values.

Use COUNT(column) when you're interested only in rows where a specific column has a value. Since it automatically ignores NULLs, it's ideal for measuring completed or populated data.

Understanding this small but important difference helps you write more accurate SQL queries, avoid common bugs, and perform better in technical interviews. The next time you need to count records in MySQL, choose the function that matches your data rather than relying on assumptions.