CODEWITHSUNDEEP
mysql
Agamemnus
Agamemnus

Reputation: 1426

Using COUNT(*) versus an individual value in MySQL

I seem to recall someone telling me a long time ago that using COUNT(*) in a MySQL statement to check if a row exists is the most efficient method (versus COUNT(some_column_name)), but I am not sure. Is this true? Should we be using COUNT(*) or COUNT(some_column_name)?

Upvotes: 2

Views: 56

Answers (2)

Parth Trivedi
Parth Trivedi

Reputation: 3832

COUNT(*) counts all rows in the result set (or group if using GROUP BY). COUNT(column_name) only counts those rows where column_name is NOT NULL. This may be slower in some situations even if there are no NULL values because the value has to be checked (unless the column is not nullable). COUNT(1) is the same as COUNT(*) since 1 can never be NULL.

To see the difference in the results you can try this little experiment:

CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);
SELECT
    COUNT(*) AS a,
    COUNT(x) AS b,
    COUNT(1) AS c FROM table1;

Result:

a   b   c 3   2   3

Upvotes: 1

Mureinik
Mureinik

Reputation: 312404

count(some_column) counts the number of non-null values in that column. Essentially, you're forcing the database to read all the values for that column (regardless of whether its done via table access or some index).

count(*) counts the number of rows in the table, thus leaving the database a larger freedom of choice with regards to which indexes to use. Theoretically, this could be faster.

Upvotes: 2

Related Questions