11–13 of 13 posts
1. This is a better fit for memcache than redis, since you can get the counts to expire automatically
2. If you are fetching paginated results with a LIMIT (and you usually are) then you can use the SQL_CALC_FOUND_ROWS prefix on your SELECT to get these counts for "free" from MySQL without needing to do your own caching (see http://dev.mysql.com/doc/refman/5.0/en/select.html )
You can also do a grouped count query in a single query, by using something like:
SELECT COUNT(*) FROM comments WHERE GROUP BY post_id;
From that he gets an array of all the counts, so he can look those up easily.
And that result can also be cached as well, of course.
Earlier quoted context omitted.
I was under the perhaps false impression that count star queries with or without where conditions are not cached on innodb tables.
Not exactly. On isam table the count(*) queries can sometimes directly lookup the answer from the index, or the table statistics. On innodb, because of transactions, this is not possible, and it needs to actually count the rows. BUT, the query itself is still cached - at least until the underlying table gets updated, which invalidates the cache (I'm not sure of the exact invalidation strategy, but it's part of MySQL…