4 ms·

Everytime I deal with mysql I ask why there is mysql. It's terrible when compared to postgres at consistency, correctness, functionality (e.g. check constraint

Everytime I deal with mysql I ask why there is mysql. It's terrible when compared to postgres at consistency, correctness, functionality (e.g. check constraints, cte, and window functions, pg_tgrm, full text search, index flexibility and that's without thinking about PostGIS, pg-routing and many of the other excellent 3rd party extensions) and error handling and nearly equivalent when it comes for basic queries that mysql even supports. Edit: forgot transactional ddls, real ones, not the ones mysql has in the pipeline.

mulmen 9y ago
The industry seems to finally be warming up to Postgres and I find that really exciting. I think MySQL popularity had a lot to do with it's willingness to say "yes". It's really easy to ship something on a database that never warns you or refuses to do the wrong thing. Postgres is "harder" to use because when you make a dumb mistake it tells you about it. This post is an oversimplification but summarizes my general impression.
captain_perl 9y ago
Half of those items are already in MySQL 5.7 or 5.8. What's terrible to you is a plus for Web 2.0 data storage - not everybody wants B&D just to insert a row or to select a timestamp. I happen to use both databases, but I recommend MySQL first for startups.
- jimktrains2 9y ago
  Your recommendation is terrible. Mysql has data consistency issues. It's simply terrible to work with. It doesn't have real data types or complex indecies. The window functions they're adding aren't all that comprehensive. Window functions and ctes are in a pre release version, compared to what? Nearly a decade of production use? There are so many fewer gotchyas with mysql it's not even funny. Just stupid things like "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'". The query planner is crap and I've actually had it hang the instance. The explain output is barely usable compared to postgres'. Postgres supports a rich set of languages to write functions in. Postgres supports rich set of column, row, and table access control. Postgres supports returning syntax on updates. As far as I know check constraints are still _silently_ ignored in mysql 5.8. silently! That illustrates exactly why I don't trust mysql and probably never will. What is one advantage that mysql has that outweighs its enormous pile of garbage it brings along? What is one advantage worth not trusting your tool to work?
  - tomnipotent 9y ago
    Many of the things you've listed are not important for a wide class of projects that MySQL is used for. Facebook, Google, and Yahoo must be fools for using MySQL - clearly don't know what they're doing /s. > What is one advantage that mysql has that outweighs Replication & performance for most workloads. I've found it helpful to think of MySQL as less of a relational database, and more of a KV database with secondary index support and a SQL api.
    - jimktrains2 9y ago
      > Replication & performance for most workloads. I'm still not sold that any slight advantage in either of these cases is worth not having a flexible and trustworthy tool. > I've found it helpful to think of MySQL as less of a relational database, and more of a KV database with secondary index support and a SQL api. And that's not what most businesses need, especially right out of the gate. From my understanding that's what those large companies you mentioned and Uber do. They also have people who contribute code to mysql or mariadb, allowing them to fix any issues they have. Most businesses do not have that luxury nor would they be best off with only a kv store.
      - tomnipotent 9y ago
        > not having a flexible and trustworthy tool. Everything you've pointed out are great examples of technical demerits... but none that really transfer over in any negative way to the business besides developer frustration. I wouldn't be surprised if MySQL got replication out so earl because it was such a POS that needed a better backup strategy, but that doesn't change the battle-hardened RDBMS it's become today. > And that's not what most businesses need Most businesses don't know what they need until they're a few years in, and database software isn't going to be on any execs top 100 list of things troubling the company. > They also have people who contribute code to mysql or mariadb Contributions that are from non-trivial deployments, supplemented with countless years running in production across millions of installs of Wordpress and phpBB thanks to cheap web hosting - I don't think there's any other database in this category that can claim it has seen more service. It's just another software tool like any other, sorta pointless to shit on it just because you don't have a problem where it works.
swsieber 9y ago
Mysql has better replication (at this point). Postgres does seem to be catching up in that area.
- jimktrains2 9y ago
  Better by what metric, and is it worth the hardship in every other area?
  - Something1234 9y ago
    I think mysql supports multimaster, and other replication formats. Postgres just supports master and slave.
    - jimktrains2 9y ago
      Not true. https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an... postgres has a variety of ways to do replication, including multimaster (mainly asynchronous). Core is also getting improvements that will make this easier to do without extensions, mainly through the infrastructure pglogicql brings. Until 5.7 I believe mysql required extensions to do multi master as well.
      - chronid 9y ago
        BDR is available in the open (aka for free :D) only up to PostgreSQL 9.4 though, and that's really the only somewhat-equivalent solution to MySQL multimaster IIRC.
        jimktrains2 9y ago
        Also not true. 9.6 is supported and the next release (scheduled soon) will support 10. Those changes will eventually end up in 12. https://www.2ndquadrant.com/en/resources/bdr/ https://www.2ndquadrant.com/en/resources/bdr/ https://blog.2ndquadrant.com/news-and-roadmap-for-bdr-multi-master-postgresql/ https://blog.2ndquadrant.com/news-and-roadmap-for-bdr-multi-... I'm not sure what you mean by somewhat equivalent. Both are primarily asynchronous multimaster approaches.

mulmen 9y ago

The industry seems to finally be warming up to Postgres and I find that really exciting. I think MySQL popularity had a lot to do with it's willingness to say "yes". It's really easy to ship something on a database that never warns you or refuses to do the wrong thing. Postgres is "harder" to use because when you make a dumb mistake it tells you about it. This post is an oversimplification but summarizes my general impression.

captain_perl 9y ago

Half of those items are already in MySQL 5.7 or 5.8. What's terrible to you is a plus for Web 2.0 data storage - not everybody wants B&D just to insert a row or to select a timestamp. I happen to use both databases, but I recommend MySQL first for startups.

jimktrains2 9y ago
Your recommendation is terrible. Mysql has data consistency issues. It's simply terrible to work with. It doesn't have real data types or complex indecies. The window functions they're adding aren't all that comprehensive. Window functions and ctes are in a pre release version, compared to what? Nearly a decade of production use? There are so many fewer gotchyas with mysql it's not even funny. Just stupid things like "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'". The query planner is crap and I've actually had it hang the instance. The explain output is barely usable compared to postgres'. Postgres supports a rich set of languages to write functions in. Postgres supports rich set of column, row, and table access control. Postgres supports returning syntax on updates. As far as I know check constraints are still _silently_ ignored in mysql 5.8. silently! That illustrates exactly why I don't trust mysql and probably never will. What is one advantage that mysql has that outweighs its enormous pile of garbage it brings along? What is one advantage worth not trusting your tool to work?
- tomnipotent 9y ago
  Many of the things you've listed are not important for a wide class of projects that MySQL is used for. Facebook, Google, and Yahoo must be fools for using MySQL - clearly don't know what they're doing /s. > What is one advantage that mysql has that outweighs Replication & performance for most workloads. I've found it helpful to think of MySQL as less of a relational database, and more of a KV database with secondary index support and a SQL api.
  - jimktrains2 9y ago
    > Replication & performance for most workloads. I'm still not sold that any slight advantage in either of these cases is worth not having a flexible and trustworthy tool. > I've found it helpful to think of MySQL as less of a relational database, and more of a KV database with secondary index support and a SQL api. And that's not what most businesses need, especially right out of the gate. From my understanding that's what those large companies you mentioned and Uber do. They also have people who contribute code to mysql or mariadb, allowing them to fix any issues they have. Most businesses do not have that luxury nor would they be best off with only a kv store.
    - tomnipotent 9y ago
      > not having a flexible and trustworthy tool. Everything you've pointed out are great examples of technical demerits... but none that really transfer over in any negative way to the business besides developer frustration. I wouldn't be surprised if MySQL got replication out so earl because it was such a POS that needed a better backup strategy, but that doesn't change the battle-hardened RDBMS it's become today. > And that's not what most businesses need Most businesses don't know what they need until they're a few years in, and database software isn't going to be on any execs top 100 list of things troubling the company. > They also have people who contribute code to mysql or mariadb Contributions that are from non-trivial deployments, supplemented with countless years running in production across millions of installs of Wordpress and phpBB thanks to cheap web hosting - I don't think there's any other database in this category that can claim it has seen more service. It's just another software tool like any other, sorta pointless to shit on it just because you don't have a problem where it works.

swsieber 9y ago

Mysql has better replication (at this point). Postgres does seem to be catching up in that area.

jimktrains2 9y ago
Better by what metric, and is it worth the hardship in every other area?
- Something1234 9y ago
  I think mysql supports multimaster, and other replication formats. Postgres just supports master and slave.
  - jimktrains2 9y ago
    Not true. https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an... postgres has a variety of ways to do replication, including multimaster (mainly asynchronous). Core is also getting improvements that will make this easier to do without extensions, mainly through the infrastructure pglogicql brings. Until 5.7 I believe mysql required extensions to do multi master as well.
    - chronid 9y ago
      BDR is available in the open (aka for free :D) only up to PostgreSQL 9.4 though, and that's really the only somewhat-equivalent solution to MySQL multimaster IIRC.
      - jimktrains2 9y ago
        Also not true. 9.6 is supported and the next release (scheduled soon) will support 10. Those changes will eventually end up in 12. https://www.2ndquadrant.com/en/resources/bdr/ https://www.2ndquadrant.com/en/resources/bdr/ https://blog.2ndquadrant.com/news-and-roadmap-for-bdr-multi-master-postgresql/ https://blog.2ndquadrant.com/news-and-roadmap-for-bdr-multi-... I'm not sure what you mean by somewhat equivalent. Both are primarily asynchronous multimaster approaches.