MySQL count rows performance

Question

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

    `select * from tbl where pk = 1`
    <em>retrieve mysql_num_rows</em>

or with

    `select count(*) as total from tbl where pk = 1`
    *fetch the array and retrieve the "total" value*

?

Answer 1

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.

Answer 2

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 3

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 4

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)