I have long said: "MySQL is the PHP of databases."
In my experience, the database is capable, but the database itself has some quirks (whether these are due to the database itself or the developers I do not know). Some things I do know for sure:
- I worked on a product which supported MsSQL, Oracle, Postgres, and MySQL. Only MySQL compared character columns in a case insensitive way which caused us some extra work, but we did fix this using the proper collation.
- I worked on a Django project. When using MySQL, a Django ORM query would pull the entire result set and hold it in memory while you iterated over it, sometimes consuming gigs of ram if we weren't careful. Iterating over a result set from Postgres would pull (presumably) only one result at a time. Whatever the case, it didn't cause memory problems like MySQL. This is not MySQL's fault, but again, it was the exceptionally problematic database.
- I'm currently working with a MySQL database which is quite troublesome because foreign-keys are not enforced. See a non-null foreign-key? You might expect it to refer to an existing record, but you'd be wrong. Again, failure of the developers, not the database.
- I also am currently working with a MsSQL database. We had to add a column to a MsSQL table; no locking, no problem. Then I had to add a column to a MySQL table, which locked the entire table for the length of the operation. MySQL was, again, problematic where another database was not.
There are other problems I have only heard of, but I will not mention those. All the above are personal experiences where MySQL was causing trouble where other databases were not. It parallels my experience with PHP, both are great tools if used right, but they seem to be misused more often then other technologies.
"MySQL database which is quite troublesome because foreign-keys are not enforced"
Not sure what this means. Innodb will enforce them and has been the default for "awhile" although if you don't configure FKs it won't automagically do it for you (superficially, a DBMS that assumed any column named user_id is a FK for table user column id, although I bet that would create some amazing bugs occasionally) If you use myisam you can hack in FKs using triggers, which is icky/foul but you only have to do it once in the design phase. Or just don't use myisam unless you need its sometimes somewhat higher speed.
Something I say a lot is its a philosophical outlook difference. Outta the box, MySQL is a cub scout who always does his best, which may or may not have anything to do with what you think is a reasonable goal. On the other hand outta the box some DB like Postgres have no sense of humor and will go on strike / curl up and die if you ask it do something somewhat imperfectly or without the proper precise etiquette. Its somewhat easy to spend some time configuring either toward your project's philosophy or your personal philosophy.
This philosophy thing is why your comparisons were insensitive... the default outta the box collation was (is?) something weird like latin1_swedish_ci and if you merely change it to latin1_general_cs or whatever it "just works" and becomes case sensitive. Well what does the helpful cub scout mysql do, it makes sure you get all the results you could possibly want, in addition to some you probably don't.
I have run into slow schema mod problem before. Solution, don't change your schema.
MySQL has been my go-to database for years because it was fast, reliable and effective. Unfortunately it hasn't been evolving as quickly as other databases, Postgres in particular.
There's a lot of quirks in MySQL that were excusable a decade a go, but now they're problems that've been solved in virtually every other database engine. Working with Sybase was never a pleasant experience, but versions a decade old have features that MySQL still doesn't have.
Every so often you'll hit the wall with MySQL, little things like applying a LIMIT in a subselect, or row length limitations, that will make you wonder why you're using MySQL at all.
Actually that's what I thought, but it's not always the case. Sometimes a good chunk of the TEXT type column is saved in the row itself (~768 characters), and when the length exceeds that, the remainder is stored in the blob area of the table store. (http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-si...)
This depends on your row format, an obscure technical issue most never have to deal with, but it can be a problem.
In my experience, the database is capable, but the database itself has some quirks (whether these are due to the database itself or the developers I do not know). Some things I do know for sure:
- I worked on a product which supported MsSQL, Oracle, Postgres, and MySQL. Only MySQL compared character columns in a case insensitive way which caused us some extra work, but we did fix this using the proper collation.
- I worked on a Django project. When using MySQL, a Django ORM query would pull the entire result set and hold it in memory while you iterated over it, sometimes consuming gigs of ram if we weren't careful. Iterating over a result set from Postgres would pull (presumably) only one result at a time. Whatever the case, it didn't cause memory problems like MySQL. This is not MySQL's fault, but again, it was the exceptionally problematic database.
- I'm currently working with a MySQL database which is quite troublesome because foreign-keys are not enforced. See a non-null foreign-key? You might expect it to refer to an existing record, but you'd be wrong. Again, failure of the developers, not the database.
- I also am currently working with a MsSQL database. We had to add a column to a MsSQL table; no locking, no problem. Then I had to add a column to a MySQL table, which locked the entire table for the length of the operation. MySQL was, again, problematic where another database was not.
There are other problems I have only heard of, but I will not mention those. All the above are personal experiences where MySQL was causing trouble where other databases were not. It parallels my experience with PHP, both are great tools if used right, but they seem to be misused more often then other technologies.