Postgresql has long been consistently faster than mysql. Count(*) is slow because it is doing what you ask. You solve this problem the same way you solve any aggregate performance problem, with a computed column.
In my experience over the years, on any moderate sized db (more than, say, 100k rows in a table) select count() has always been faster under mysql - both myisam and innodb (although innodb doesn't claim to be 100% accurate all the time).
Why should I* have to keep a computed column when the core engine has all the data all the time? And it's something pretty fundamental to the data - how much of it is actually in there.
I think we're misunderstanding each other. I thought you were claiming mysql is faster for "most common uses cases", which is why I pointed out that it is actually the opposite. Yes, mysql is faster at count(), and yes it is because it doesn't actually do a count() and just returns a guess. If your argument that postgresql is bad is that it doesn't return fake data, then it says more about you than about postgresql.
>Why should I have to keep a computed column when the core engine has all the data all the time?
The core engine does not have that information, keeping that information for no reason would be foolish. You should keep a computed column for performance, obviously. That is your complaint remember? How is this any different than "select users.id, users.name, count(photos.id) from users left join photos on photos.user_id = users.id where users.id = ?" being slow? How do you solve that being slow? You use a computed column. The fact that you complain about a complete non-issue because you inexplicably refuse to use the standard solution to the problem in this one particular instance of the general pattern is neither logical nor reasonable.
I may be wrong here, but I think the difference is largely around how things are viewed differently between experts and non-experts.
Experts understand that "estimate the number of rows" and "count the number of rows" are two different operations. They know that "count()" is supposed to count, not estimate and that in MySQL it does an estimate instead. They even know a simple way to provide quick estimates.
Non-experts know that "count()" is faster in one tool than another.
Certainly, mysql's existence has always been based on "we're the db for people who don't know anything about dbs". But in this particular case, the person I responded to does know that mysql isn't doing a count, he pointed it out himself.
