6 ms·

Terrible choices: MySQL (2014)

45 points by StreamBright 11y ago

TazeTSchnitzel 11y ago
MySQL is the PHP of databases (speaking as a PHP contributor). It's awful in the same kinds of ways.
- mamon 11y ago
  MySQL does not deserve to be called a database at all - as the article describes, it does not implement basic database features correctly.
  - hughperkins 11y ago
    I think it's fairer to say that: it doesnt necessarily implement them in a standards-compliant way. Which I dont mean to imply it implements everything, in a non-standard way, it's missing a bunch of stuff. But it does have a lot of standard database features, like transactions and so on, but a lot of stuff is ... odd. for example, 'row_number()' is missing. You have to use session variables.
    - mamon 11y ago
      "Non-standards-compliant way" is the long-time default engine, MyISAM not supporting transactions and locking. Fortunately they moved to InnoDB as default recently, but the very fact that it took them so long tells much about their attitude towards DB standards compliance.
  - onion2k 11y ago
    it does not implement basic database features correctly For 99% of use cases that doesn't really matter though. Google, Facebook, Github, etc all started out using MySQL to drive the data behind their services. Some of them still do. Yes, you have to work around the problems, but it's still a very powerful piece of technology.
- executesorder66 11y ago
  What is your opinion of MariaDB?
  - TazeTSchnitzel 11y ago
    I don't know a lot about it, but AIUI it's mostly a fork of MySQL with more features, rather than a fundamental improvement. MySQL's problem isn't that it's owned by Oracle.
  - leesalminen 11y ago
    I switched from MySQL to MariaDB ~2 years ago and have been quite pleased. A little while back, I needed to use a REGEX_REPLACE() function. I knew MySQL didn't have it built-in, and didn't want to build any half assed userland function for it. I held my breath and looked it up in MariaDB docs, and there it was! Made my morning. It's the little things that made me happy to switch. Also, my understanding is that InnoDB has some performance gains.
spacemanmatt 11y ago
Article is from 2014; does all of this still apply? I'm just trying to understand the relevance, other than MySQL is a flaming death trap which everyone should be warned about regularly.
- kidmenot 11y ago
  I would be happy to be proven wrong (thankfully I have dealt exclusively with Postgres in the last two years or so), but I think most of the warts and shortcomings the post talks about are still extant. Off the top of my head, I can add that you can't have subselects in views (which is preposterous), nor can you materialize views. Also, I don't believe you can have function-based indexes, and most certainly you can't pivot tables easily and CTEs are not supported. I think 5.7 (if it's been released) finally brought the ability to create multiple triggers for the same event. Until 5.6 you could specify only one. In short: MySQL is no fun. Choose Postgres or any other decent system, but just steer clear of the damn dolphin.
  - spacemanmatt 11y ago
    > MySQL is no fun. Choose Postgres or any other decent system, but just steer clear of the damn dolphin. Ahahahahahahaha. Thank you. That was amusing and also helpful.
chris_wot 11y ago
Even though this is an old article, I stumbled on pgloader! http://pgloader.io/ http://pgloader.io/ Sounds like this might get me out of future MySQL pain...
- smt88 11y ago
  I used pgloader for a project, and it was harder to use (and lossier) than I was expecting. Make sure you carefully look what you're losing in the conversion and are able to add it back (either during conversion by hooking into pgloader or after conversion).
  - chris_wot 11y ago
    Do you have any further details on what you lost? Very curious - especially to know if it would be considered a bug or design flaw somewhere in the program itself, or a feature mismatch between MySQL and PostgreSQL.
    - smt88 11y ago
      Feature mismatch. Something related to constraints. It wasn't a showstopper at all.
edejong 11y ago
I remember being introduced to MySQL a long time ago (around 2004). When I noticed its standards incompliance, buggy indexing, weird collating behaviour and incomplete query optimizer, I was repeatedly told these quirks will soon be resolved and it was 'only a matter of time'. Something to keep in the back of your head when you see similar situations with other open source solutions.
- noja 11y ago
  with other <strike>open source</strike> solutions.
  - endymi0n 11y ago
    While it most definitely is open source, it nicely shows the difference between an open source project and an open source product (which, on top, is cannibalizing the other, ridiculously high priced mainline inhouse DB)
eterm 11y ago
Without wanting to discuss the merits of MySQL (I don't use it), is case insensitive collation that much of a problem? I note that case insensitive collation is also a default for MS-SQL.
- chris_wot 11y ago
  It's not just case-insensitive, it's accent insensitive. That could cause problems for any language that uses accents.
lucian1900 11y ago
So the solution to making get_or_create work is to downgrade the isolation level? That seems like the flaw is with get_or_create in the first place. I've found collisions to be quite nice for doing "upserts", either on a constraint or predictable id.
mediascreen 11y ago
For anyone that still wants/has to use MySQL, there is always MariaDB - a drop in replacement that is evolving a bit faster. https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/ https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/
- 1812Overture 11y ago
  Whoa there. Not completely drop in. Definitely thoroughly go through the documentation to see where MariaDB has diverged from MySQL over the years.
onion2k 11y ago
I'm quite sure you could do a breakdown of any technology and find enough problems that it looks like a bad choice. What tech you use is always a compromise between the benefits of what it's good for and the problems you need to work around. In the case of MySQL it's ubiquitous across hosting services, easy to use, very fast once you've tuned it a bit, and there's clients for pretty much every language. You need to weigh those things against all its problems, and often you'll find it's actually a good option if you're doing web work that you need to deploy to environments you have little control over - less so now than 5 years ago admittedly, but that is the reason MySQL is so popular - because before the devops trend that's got web teams running their own servers most sysadmins made MySQL the only database available.
- smt88 11y ago
  > it's ubiquitous across hosting services So? No one needs 10 different hosting services. You only need one, and you've been able to choose a (cheap!) one that offers Postgres for many years now. > easy to use I strongly disagree, and the entire article is basically rebutting this assertion. It seems easy to use, but silently failing, breaking promises, and being noncompliant with standards are all issues that make MySQL harder to use. > very fast once you've tuned it a bit You can tune Postgres as well. It actually optimizes its queries much better than MySQL does, so you can go longer without starting to optimize. > and there's clients for pretty much every language Unless you're using some fringe-y language, this isn't an issue either. Some languages have gotten a Postgres client before MySQL, simply because their communities have a culture that's more similar to Postgres.
mathnode 11y ago
I have been a mysql dba for years; I don't recommend mysql for anything or anyone. Oracle are adding glitter, not polish. If you still really want or need mysql please do check out mariadb, even old versions like mariadb 5.3 onwards added a lot of features and fixes for old problems from before the Sun acquisition (Remember, MariaDB was forked with the Sun buyout or MySQL AB, not Oracle). It's not entirely a drop in replacement though, read the manual and test your configs! What MariaDB/MySQL is still really good at, is scaling horizontally pretty quickly, just be sure to separate out different schema workloads into different server instances, and don't make your schema overly complex. Not that it can get too complex anyway with SQL/PSM heh-snort snort. Want to scale bigger and faster, and maybe with an even more highly tuned dataset per-cluster? Cassandra. Lots of Oracle and IBM business is being slurped up by Cassandra and Datastax. Want mixed database workloads and all the RDBMS management, SQL and PL goodies? PostgreSQL.
- deleted 11y ago
  [deleted]
- chris_wot 11y ago
  I'm genuinely curious - under what circumstances would you recommend MySQL or even MariaDB over PostgreSQL?
  - mathnode 11y ago
    Since Streaming Replication and pg_basebackup was introduced in PostgreSQL; I generally wouldn't. MariaDB 10 and onwards has some fun features though if you are stuck with it. The CONNECT and TokuDB storage engines are very useful and interesting. Parallel, Multi-source, and GTID replication with multiple domains is very easy.

45 points by StreamBright 11y ago

TazeTSchnitzel 11y ago

MySQL is the PHP of databases (speaking as a PHP contributor). It's awful in the same kinds of ways.

mamon 11y ago
MySQL does not deserve to be called a database at all - as the article describes, it does not implement basic database features correctly.
- hughperkins 11y ago
  I think it's fairer to say that: it doesnt necessarily implement them in a standards-compliant way. Which I dont mean to imply it implements everything, in a non-standard way, it's missing a bunch of stuff. But it does have a lot of standard database features, like transactions and so on, but a lot of stuff is ... odd. for example, 'row_number()' is missing. You have to use session variables.
  - mamon 11y ago
    "Non-standards-compliant way" is the long-time default engine, MyISAM not supporting transactions and locking. Fortunately they moved to InnoDB as default recently, but the very fact that it took them so long tells much about their attitude towards DB standards compliance.
- onion2k 11y ago
  it does not implement basic database features correctly For 99% of use cases that doesn't really matter though. Google, Facebook, Github, etc all started out using MySQL to drive the data behind their services. Some of them still do. Yes, you have to work around the problems, but it's still a very powerful piece of technology.
executesorder66 11y ago
What is your opinion of MariaDB?
- TazeTSchnitzel 11y ago
  I don't know a lot about it, but AIUI it's mostly a fork of MySQL with more features, rather than a fundamental improvement. MySQL's problem isn't that it's owned by Oracle.
- leesalminen 11y ago
  I switched from MySQL to MariaDB ~2 years ago and have been quite pleased. A little while back, I needed to use a REGEX_REPLACE() function. I knew MySQL didn't have it built-in, and didn't want to build any half assed userland function for it. I held my breath and looked it up in MariaDB docs, and there it was! Made my morning. It's the little things that made me happy to switch. Also, my understanding is that InnoDB has some performance gains.

spacemanmatt 11y ago

Article is from 2014; does all of this still apply? I'm just trying to understand the relevance, other than MySQL is a flaming death trap which everyone should be warned about regularly.

kidmenot 11y ago
I would be happy to be proven wrong (thankfully I have dealt exclusively with Postgres in the last two years or so), but I think most of the warts and shortcomings the post talks about are still extant. Off the top of my head, I can add that you can't have subselects in views (which is preposterous), nor can you materialize views. Also, I don't believe you can have function-based indexes, and most certainly you can't pivot tables easily and CTEs are not supported. I think 5.7 (if it's been released) finally brought the ability to create multiple triggers for the same event. Until 5.6 you could specify only one. In short: MySQL is no fun. Choose Postgres or any other decent system, but just steer clear of the damn dolphin.
- spacemanmatt 11y ago
  > MySQL is no fun. Choose Postgres or any other decent system, but just steer clear of the damn dolphin. Ahahahahahahaha. Thank you. That was amusing and also helpful.

chris_wot 11y ago

Even though this is an old article, I stumbled on pgloader! http://pgloader.io/ http://pgloader.io/ Sounds like this might get me out of future MySQL pain...

smt88 11y ago
I used pgloader for a project, and it was harder to use (and lossier) than I was expecting. Make sure you carefully look what you're losing in the conversion and are able to add it back (either during conversion by hooking into pgloader or after conversion).
- chris_wot 11y ago
  Do you have any further details on what you lost? Very curious - especially to know if it would be considered a bug or design flaw somewhere in the program itself, or a feature mismatch between MySQL and PostgreSQL.
  - smt88 11y ago
    Feature mismatch. Something related to constraints. It wasn't a showstopper at all.

edejong 11y ago

I remember being introduced to MySQL a long time ago (around 2004). When I noticed its standards incompliance, buggy indexing, weird collating behaviour and incomplete query optimizer, I was repeatedly told these quirks will soon be resolved and it was 'only a matter of time'. Something to keep in the back of your head when you see similar situations with other open source solutions.

noja 11y ago
with other <strike>open source</strike> solutions.
- endymi0n 11y ago
  While it most definitely is open source, it nicely shows the difference between an open source project and an open source product (which, on top, is cannibalizing the other, ridiculously high priced mainline inhouse DB)

eterm 11y ago

Without wanting to discuss the merits of MySQL (I don't use it), is case insensitive collation that much of a problem? I note that case insensitive collation is also a default for MS-SQL.

chris_wot 11y ago
It's not just case-insensitive, it's accent insensitive. That could cause problems for any language that uses accents.

lucian1900 11y ago

So the solution to making get_or_create work is to downgrade the isolation level? That seems like the flaw is with get_or_create in the first place. I've found collisions to be quite nice for doing "upserts", either on a constraint or predictable id.

mediascreen 11y ago

For anyone that still wants/has to use MySQL, there is always MariaDB - a drop in replacement that is evolving a bit faster. https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/ https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/

1812Overture 11y ago
Whoa there. Not completely drop in. Definitely thoroughly go through the documentation to see where MariaDB has diverged from MySQL over the years.

onion2k 11y ago

I'm quite sure you could do a breakdown of any technology and find enough problems that it looks like a bad choice. What tech you use is always a compromise between the benefits of what it's good for and the problems you need to work around. In the case of MySQL it's ubiquitous across hosting services, easy to use, very fast once you've tuned it a bit, and there's clients for pretty much every language. You need to weigh those things against all its problems, and often you'll find it's actually a good option if you're doing web work that you need to deploy to environments you have little control over - less so now than 5 years ago admittedly, but that is the reason MySQL is so popular - because before the devops trend that's got web teams running their own servers most sysadmins made MySQL the only database available.

smt88 11y ago
> it's ubiquitous across hosting services So? No one needs 10 different hosting services. You only need one, and you've been able to choose a (cheap!) one that offers Postgres for many years now. > easy to use I strongly disagree, and the entire article is basically rebutting this assertion. It seems easy to use, but silently failing, breaking promises, and being noncompliant with standards are all issues that make MySQL harder to use. > very fast once you've tuned it a bit You can tune Postgres as well. It actually optimizes its queries much better than MySQL does, so you can go longer without starting to optimize. > and there's clients for pretty much every language Unless you're using some fringe-y language, this isn't an issue either. Some languages have gotten a Postgres client before MySQL, simply because their communities have a culture that's more similar to Postgres.

mathnode 11y ago

I have been a mysql dba for years; I don't recommend mysql for anything or anyone. Oracle are adding glitter, not polish. If you still really want or need mysql please do check out mariadb, even old versions like mariadb 5.3 onwards added a lot of features and fixes for old problems from before the Sun acquisition (Remember, MariaDB was forked with the Sun buyout or MySQL AB, not Oracle). It's not entirely a drop in replacement though, read the manual and test your configs! What MariaDB/MySQL is still really good at, is scaling horizontally pretty quickly, just be sure to separate out different schema workloads into different server instances, and don't make your schema overly complex. Not that it can get too complex anyway with SQL/PSM heh-snort snort. Want to scale bigger and faster, and maybe with an even more highly tuned dataset per-cluster? Cassandra. Lots of Oracle and IBM business is being slurped up by Cassandra and Datastax. Want mixed database workloads and all the RDBMS management, SQL and PL goodies? PostgreSQL.

deleted 11y ago
[deleted]
chris_wot 11y ago
I'm genuinely curious - under what circumstances would you recommend MySQL or even MariaDB over PostgreSQL?
- mathnode 11y ago
  Since Streaming Replication and pg_basebackup was introduced in PostgreSQL; I generally wouldn't. MariaDB 10 and onwards has some fun features though if you are stuck with it. The CONNECT and TokuDB storage engines are very useful and interesting. Parallel, Multi-source, and GTID replication with multiple domains is very easy.