Faster: count(*) or count(column)?

[lordvader]

When getting a count of all entries that fulfill whatever criteria, which is faster:

select count(*) or select count(column_name_that_is_a_primary_index)

 

Thanks

[bluejay002]

from mysql:

 

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

 

mysql> SELECT COUNT(*) FROM student;

 

This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB and BDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

 

[hitman6003]

InnoDB employs MultiVersioning Concurrency Controls...which is a fancy way of saying that when you initiate a transaction (and all queries are transactions, you don't have to issue "START TRANSACTION / COMMIT" because autocommit is on by default) you will see the database in that state at all times until the transaction is committed.  So, if your transaction is a "COUNT" query it will return the number of rows present in the table at the start of the transaction (this behavior can be changed via the SQL mode).

 

Additionally, while MyISAM keeps track of the current number of rows at any given time (and uses that number if the situations provided by the manual above), in any other situation it has the very real potential of locking the entire table while it does a row count.  Not a big deal with 100 rows in a table, but with 100 million, you're talking a long time.

 

InnoDB will always do a row count when a COUNT query is issued.  So, if you do "SELECT COUNT(*) FROM table" it will scroll through and lock/unlock each row in sequence as it counts them.