MySQL count rows performance

Question

For an InnoDB storage is it better to count the total number of records by

• Using mysql_num_rows on

  select * from tbl where pk = 1
  

or by

• fetching the array and retrieve the "total" value from

  select count(*) as total from tbl where pk = 1
  

?

Answer 1

Absolutely the latter. It can grab the value direct from the PK's index, whereas the former almost certainly requires a table scan (unless every single column is part of an index; and even then, it has to grab values from all over the indexes). Then depending on how you're connecting to the DB, there's a large amount of data transit just to get a count.

explain can help here. In this case, it'll tell you that the select is optimized away.

Answer 2

In addition to Zxpro's answer, there is also the MySQL internal effort:

select * from tbl where pk = 1

forces MySQL to retrieve matching rows physically; whereas

select count(*) as total from tbl where pk = 1 

allows MySQL to count entries in the primary key, without retrieving any table rows

Answer 3

The latter is most likely to perform better since your are only transmitting a single integer whereas, in the first scenario, you'll be sending a whole lot more data.