Count Rows in MySQL: The 5 Best Ways

How-to basics · Updated November 22, 2026

The fastest way to count rows in MySQL is with the COUNT(*) function in a query like SELECT COUNT(*) FROM table_name;. That returns the total number of rows in the table, and it is the correct answer for most everyday counting needs.

Why COUNT(*) Is the Default Answer

MySQL’s COUNT() is an aggregate function that counts rows or non-NULL values. When you write COUNT(*), MySQL counts every row in the result set, including rows where all columns are NULL. That makes it the most reliable way to get a true row total.

For a table named orders, the query is simply:

SELECT COUNT(*) FROM orders;

That returns a single number, for example 1250. If you want a column alias for readability:

SELECT COUNT(*) AS total_orders FROM orders;

This works on any table, any storage engine, and any MySQL version. It is the baseline you should start with.

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

People often confuse these three forms, but they answer different questions.

  • COUNT(*) counts every row in the result set.
  • COUNT(column_name) counts only rows where that column is not NULL.
  • COUNT(DISTINCT column_name) counts the number of unique non-NULL values in that column.

For example, if you have a users table with an email column that has 100 rows but 3 NULL emails, then COUNT(*) returns 100, COUNT(email) returns 97, and COUNT(DISTINCT email) returns the number of unique email addresses (which could be 95 if two pairs are duplicates).

Use COUNT(column) when you need to know how many rows have actual data in that column. Use COUNT(DISTINCT column) when you need unique values, like counting distinct customers from a sales table.

Counting Rows with WHERE Conditions

Often you don’t want the whole table—you want rows that match a condition. Add a WHERE clause to filter first:

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

That counts only shipped orders. You can combine with any valid condition, including dates, ranges, or joins. For example, counting orders placed in 2024:

SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

This is the standard way to get a filtered count, and it works with indexes efficiently when the column in the WHERE clause is indexed.

The Performance Trap: COUNT(*) on Large Tables

Here is the catch: SELECT COUNT(*) on a large table can be slow. In the default InnoDB storage engine, MySQL must scan the entire table (or an index) to count rows because InnoDB does not store an exact row count internally. On a table with millions of rows, that query can take seconds or longer.

MyISAM, the older storage engine, stores the exact row count, so COUNT(*) without a WHERE is instant. But InnoDB is the default and recommended engine, so you will usually face the scan.

To speed up counting on InnoDB, you have a few options:

  • Count over a small index: SELECT COUNT(*) FROM orders USE INDEX (primary); can be faster if the primary key is small, but it still scans the whole index.
  • Use an approximate count from the information schema: SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders'; This returns an estimate, not exact, but it is instant.
  • Maintain a separate counter table that you increment with triggers or application logic. This is exact but adds complexity.
  • For filtered counts, ensure the WHERE column is indexed so MySQL can use an index range scan instead of a full table scan.

If you need an exact count for a huge table, be prepared for a slow query. If an estimate is acceptable, use the information schema approach.

Counting Rows in a Result Set vs. Using FOUND_ROWS()

Sometimes you run a SELECT with LIMIT and want to know how many total rows would have matched without the limit. The old way was SQL_CALC_FOUND_ROWS and FOUND_ROWS(), but that is deprecated in MySQL 8.0.17 and removed in later versions. The modern approach is to run two queries: one with COUNT(*) and the same WHERE, and one with the LIMIT for the actual page of data.

For example, for pagination:

SELECT COUNT(*) FROM products WHERE category = 'electronics';

Then separately:

SELECT * FROM products WHERE category = 'electronics' LIMIT 20 OFFSET 40;

This is clear and works on all versions. Do not rely on FOUND_ROWS() in new code.

Counting Rows in a JOIN or Subquery

When joining tables, COUNT(*) counts the number of rows in the joined result. For example, counting how many orders each customer has:

SELECT c.customer_id, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

That returns one row per customer with the number of their orders. Be careful: if a customer has no orders, they won’t appear because the join is inner. Use a LEFT JOIN to count zero-order customers:

SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

Here COUNT(o.order_id) counts only non-NULL order IDs, so customers with no orders show 0. This is a common pattern worth knowing.

You can also count rows from a subquery by wrapping it:

SELECT COUNT(*) FROM (
    SELECT customer_id FROM orders WHERE total > 100
) AS high_value_customers;

That counts the number of rows returned by the subquery.

Frequently Asked Questions

Does COUNT(*) count NULL values?

Yes. COUNT(*) counts every row regardless of NULLs in any column. If you want to exclude rows where a specific column is NULL, use COUNT(column_name) instead.

Why is COUNT(*) so slow on my big table?

Because InnoDB does not keep an exact row count. MySQL must scan the entire table or index to count. For tables with millions of rows, that scan takes time. Use an approximate count from information_schema if you don’t need exactness, or maintain a counter table.

Can I get the row count without running a query?

Not exactly. You can query information_schema.TABLES for an estimate, but it is not guaranteed to be exact. For exact counts, you must run COUNT(*) or maintain your own counter.

What is the difference between COUNT(1) and COUNT(*)?

Functionally, they are identical in MySQL. Both count every row. Some older folklore says COUNT(1) is faster, but modern MySQL optimizes both to the same plan. Use COUNT(*) for clarity.

How do I count distinct values in a column?

Use COUNT(DISTINCT column_name). For example, SELECT COUNT(DISTINCT customer_id) FROM orders; returns the number of unique customers who have orders.

Does COUNT(*) work with GROUP BY?

Yes. When used with GROUP BY, COUNT(*) returns the number of rows in each group. That is the standard way to count items per category, per day, or per any grouping column.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.