As with most similar rants against the earlier days of MySQL, the majority of these points are based on old versions and don't reflect how things have been for almost a decade.
The question was what made me switch, which was all the crap MySQL does to a dev. That blog post is the best that I know of that captures the fractal of bad design that is MySQL. While, yes, it matches my understanding that some of those points are no longer valid, it doesn't match my understanding that most of the points are no longer valid. (E.g., just a few years ago, when I last used MySQL, I seem to remember that it still struggled w/ UTF-8, still struggled with the definition of what a "key" is, and still struggled with functional dependencies.)
Postgres is a great choice too, and for most intents and purposes, Postgres or MySQL is the correct default choice for a database, so I'm not trying to persuade you to switch back. Just clarifying that almost none of the points in that article are still valid, including UTF-8 handling. The author themselves put a disclaimer at the top of the article stating as much. Not quite sure what you mean by "definition of a key" or "struggled with functional dependencies", so I can't speak to those directly.
E.g., the following used to be a statement in MySQL, at the time I used it; I presume it still is:
CREATE KEY …
It is the primary form of this alias:
CREATE INDEX
which is what the command does (it creates an index; it does not "create" a "key".) But since the form of the command with the correct words is itself an alias, the wrong form gets output any time MySQL self-describes something, generating endless confusion from people thinking a set of columns are a key, when they actually may or may not be. (They might also be a key, but index does not imply key.)
> struggled with functional dependencies
E.g., in GROUP BYs:
SELECT a FROM table GROUP BY b;
where a is not a functional dependency of b. (The statement is thus nonsense.) MySQL will permit this statement to execute, and generate undefined results for column `a` in the output.
In the day I used it, UTF-8 was not the default (the default was latin1, IIRC). Even better, the "utf8" encoding wasn't UTF-8. It was like UTF-8 except where it wasn't. Generated endless numbers of "ah crap, this table/DB isn't UTF-8 and will need a migration."
Simple date math in SELECTS would result in idiocy. (The output of SELECT date_a - date_b was something like "stringify the dates, remove the hyphens, cast to int, subtract" — yes, that sounds crazy, but yet that was a bug I had to work out once. There's a separate DATEDIFF function or something to do the right thing, but the point is that the naïve thing neither errors nor does anything sensible.)
> it creates an index; it does not "create" a "key"
I'm still not sure what you mean. By key, do you mean a unique key? With some quick searching, I'm not finding any difference in the Postgres docs.
> MySQL will permit this statement to execute
That was disabled by default in 5.7, 6 years ago. I totally agree that it should have been turned on by default earlier, but isn't a valid criticism of modern MySQL.
> Simple date math in SELECTS would result in idiocy
I've never tried that before, and it looks like that hasn't changed. No idea why that doesn't throw an error.
Since 8.0 (April 2018), schema changes are transactional, but you can't include DML changes in the same transaction as a DDL. I haven't found that to be a dealbreaker, but would certainly be nice to have.
No, I mean key[1]. A key is a set of attributes that uniquely identifies a row. The primary key in a table is typically one such key, but tables may also have other such keys. In particular, if the primary key is a surrogate key, there is often another natural key. "Key" is just the general form of all of those things. A key MUST be non-null & unique in most RDBMSs, or it trivially isn't a key.
But an index is just a datastructure to speed up access. You almost always cover the key with an index, as keys are generally the means with which your going to look up other attributes. But one often has other indexes that cover non-keys, e.g., if one wants to search based on some non-key attribute.
So naming the create index command "CREATE KEY" is kind of dumb. I don't think they'll remove it, as it would be a breaking change. (Probably even changing the SHOW CREATE TABLE output to use CREATE INDEX would be breaking to somebody, and I don't see MySQL as being willing to do it…)
> That was disabled by default in 5.7, 6 years ago. I totally agree that it should have been turned on by default earlier, but isn't a valid criticism of modern MySQL.
So it has! I've used MySQL within the last 6 years, and definitely have hit this, so… IDK. It was 5.<something> but that's not really saying a lot given how long the 5.x series persisted. We were on a managed service, and I wonder if our service host was holding us back, here.
Also, at least in the Docker container, the default encoding does seem to at least be utf8mb4, which is good, though I kinda wish they'd just get rid of "utf8" and make utf8mb4 utf8, but, this'll probably do the job. So, good to see MySQL has improved, I guess, but for me personally it took much too long.
