Oh how many businesses spend countless thousands on incompitent contractors when all they needed was someone to simply run EXPLAIN.
The problem with database optimization isn't that "people don't understand it well", it's that database optimization is inherently obscure and difficult. With most code, performance is a local effect: you can look at the code and see why it's slow. With some harder problems, performance is data-dependent and you need to look very closely.
With databases, it's worse still: performance is a feature of the schema, configuration, and (worse still) the quirks of the software (ex: I never knew MySQL can't optimize count(*) queries!) . They can be useful, but they're hugely complicated. In general, using a database without an expert DBA is a recipe for poor scalability.
Almost no database optimizes this. What they do is count the rows. MyISAM (one of the storage engines for MySQL) heavily optimizes this by having a row count in the table data.
This turns out not to be that useful because who wants to count the number of rows in a table? You usually want to count a subset of those rows. Like, how many comments are there for post_id 7.
So, MySQL isn't unoptimized for this.
That was my point: it's not a slam against MySQL per se, it's a slam against the whole development metaphor, which seems to derive unholy pleasure in hiding critically important implementation details from the developer.