For an InnoDB storage is it better to count the total number of records by
Using
mysql_num_rowsonselect * 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
?
Add a comment
|
4 Answers
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.
Comments
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
Comments
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.
3 Comments
Andomar
MySQL doesn't actually transmit data before you call
mysql_fetch_array
Wolfgang
so in that case why isnt the first one better?
Bryan Menard
MySQL will still have the query results buffered in some way until the data is completely fetched.
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)
