I've read previously that SELECT COUNT(*) is not performant in MySQL and should be regularly avoided. (As a side question, is this only the case for a large number of rows?)
I'm using PHP Data Objects (PDO) to access MySQL.
How should I be counting rows with performance considerations in mind?
COUNT(*), as far as I am aware is actually more efficient than using COUNT(columnname). If you are looking for a row count, these will be way more efficient then trying to count rows via a PDO or MySQL (which would most likely have to pull all rows, given that you are not just executing a select with count()).
I am unsure of any other way to get a count out, but that should be just fine, depending on what you are doing. The main item you should concentrate on is making sure your tables have proper indexes setup as this will dramatically increase the MySQL speed etc, if properly done.
If you're using MyISAM tables, it does not hit performance. MySQL caches number of rows in MyISAM tables. This is why it is able to instantly answer COUNT(*). In this case, MySQL would simply read number of rows in the table from stored value.
[EDIT]: From MySQL Performance Blog:
For InnoDB: If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions.
In real applications there are much more queries of second type rather than first type so it is typically not as bad problem as it may look. Most typically count of rows is needed by admin tools which may show it in table statistics, it may also be used in application stats to show something like “We have 123.345 users which have uploaded 1.344.656 images” but these are normally easy to remove.
So remember Innodb is not slow for ALL COUNT() queries but only for very specific case of COUNT() query without WHERE clause.
Also interesting read for you:
PDO is not really relevant in running a specific count query. PDO is just how you connect to MySQL. You should not use count(colname) if you just need a row count, you should use count(*). If you specify colname and that column is not specified as NOT NULL, it will be slow. http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/
MyISAM tables cache the count, so a count() on a MyISAM table is always immediate. InnoDB takes a while, since the state of the table can be in flux and you end up with an estimate. You can do a count(colname) for InnoDB, where colname is an auto increment field. That will be fairly fast and accurate, although never as fast as count() on MyISAM tables.