MySQL count rows performance

For an InnoDB storage is it better to count the to开发者_运维知识库tal 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
  

?

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.

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

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.

If "pk" is the primary key, there can only be one record, so the answer is going to be either 0 or 1, so you don't really need to count them anyway.

But yes, the latter.

InnoDB does not JUST need to check the index, it also needs to check that the row(s) are visible to the current transaction, because of MVCC. But this is a detail. It'll use a covering index anyway (which is irrelevant if pk is the primary key, as that is always clustered)