Should I use COUNT(*) to get all the records in MySQL?

Whenever you want all the values like not null for a column then use count(*). This is faster than using count() method.

The syntax to use count(*) is as follows −

select count(*) as anyVariableName from yourTableName;

To understand the above concept, let us first create a table. The query to create a table is as follows −

mysql> create table CountingDemo    -> (    -> BookId int    -> ); Query OK, 0 rows affected (0.60 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into CountingDemo values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into CountingDemo values(); Query OK, 1 row affected (0.17 sec) mysql> insert into CountingDemo values(200); Query OK, 1 row affected (0.12 sec) mysql> insert into CountingDemo values(300); Query OK, 1 row affected (0.16 sec) mysql> insert into CountingDemo values(); Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from CountingDemo;

Output

+--------+ | BookId | +--------+ |    100 | |   NULL | | 200 | | 300 | | NULL | +--------+ 5 rows in set (0.00 sec)

Suppose your column does not have null value then count(*) and count() gives same result.

But in our example BookId column is having some null values. In this case, both count(*) and count() gives different results.

Here is the query that use count(*) −

mysql> select count(*) as AllValue from CountingDemo;

Output

+----------+ | AllValue | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)

Her is the query that use count() and gives another result since it won’t consider counting null values. The query is as follows −

mysql> select count(BookId) as AllvalueWhichisNotnull from CountingDemo;

Output

+------------------------+ | AllvalueWhichisNotnull | +------------------------+ | 3 | +------------------------+ 1 row in set (0.00 sec)