3 ms·
I know only three reasons for that: 1) MySQL is bundled in so many low cost PHP hosting services that it is an almost forced choice if you're on a tight budget
by pmontra 12y ago
I know only three reasons for that:
1) MySQL is bundled in so many low cost PHP hosting services that it is an almost forced choice if you're on a tight budget and presumibly at a very small scale. But anything would do there, maybe even a SQLite backend. Furthermore you usually don't have to really "know" the database you're using if all you do are basic selects, inserts and updates. Chances are that your code will work on any DB and MySQL will be perfectly fine for that kind of applications.
2) At large scale, setting up a cluster of MySQL servers is a more established practice than setting up a cluster of PostgreSQL servers. This is slowly changing as replication features are added to PostgreSQL's core and BDR will be fully developed for 9.5 or 9.6 but it will take time before you find as many experts of PostgreSQL than you have for MySQL.
3) The diagramming tool of MySQL Workbench is far better that anything you can get for free for PostgreSQL. The closest match is Valentina Studio which is not as usable.
I can provide plenty or arguments for not using MySQL though. I'll leave them to a search for why mysql sucks but I want to add some of mine. I'm migrating a project from PostgreSQL to MariaDB (drop in compatible with MySQL). The customer made the choice not because of technical reasons and I don't think I can get into the details. The annoying facts we are facing:
1) A whole mess with TIMESTAMPS that is getting sorted out only with MySQL 5.6 and MariaDB 10's switches for 5.6 compatibility http://shankargopal.blogspot.it/2013/03/mysql-566-timestamp-columns-and-default.html http://shankargopal.blogspot.it/2013/03/mysql-566-timestamp-... ("In MySQL versions prior to 5.6, behavior of columns with default values for the TIMESTAMP column type is pretty much nonstandard [...]")
2) A pretty horrible procedural language adding complications over the PostgreSQL one and lacking some features we gave for granted. We must do some processing inside the database not to move whole tables to the application server so we have a few stored procedures. What used to be a < 20 lines procedure in PostgreSQL is becoming a 50+ lines little monster in MySQL/MariaDB because, for example, they can't return directly a resultset unless it comes straight from a SELECT. We're doing some processing on those SELECTs so we must create a temporary table, insert into it and select from it at the end. Ah, inserting 1100 records (INT, VARCHAR) into that temporary table took 40 seconds. We googled around and discovered that creating it with ENGINE=MEMORY fixes the problem. But even writing that to disk... 40 seconds?
3) The lack of row_number also gave us some problems in grouping records on some columns and picking the ones with the most recent timestamp for each group. See http://stackoverflow.com/questions/1895110/row-number-in-mysql http://stackoverflow.com/questions/1895110/row-number-in-mys... for examples. We built our query over this answer http://stackoverflow.com/a/9652359 http://stackoverflow.com/a/9652359
In general, MySQL has a pattern of being the only database among the big relational ones that doesn't have feature X or supports it in a non standard way.
4) MySQL's EXPLAIN doesn't seem as informative as PostgreSQL's EXPLAIN ANALYZE but maybe we're still missing something.
Summing it up, IMHO MySQL is OK for INSERT/SELECT/UPDATE applications with simple queries but this experience is confirming my years long policy of trying to steer clear from it.
Edit:
5) If you're using Ruby, the mysql driver (mysql2) could be slower than the PostgreSQL one (pg). I have a seeding script written in Ruby and it got unbeliveably slow with MySQL (20 times as slow). I opened an issue and you find the details there https://github.com/brianmario/mysql2/issues/623 https://github.com/brianmario/mysql2/issues/623
One of the mysql2 maintainers has been kind and helped me to fix the problem in this particular scenario (grouping inserts together) but it seems that the PostgreSQL driver is more efficient at sending data to the DB (it uses prepared statements, mysql2 doesn't yet) so your application could have a performance boost by using PostgreSQL over MySQL.
Be careful: this problem is Ruby specific, most probably not due to the database, and probably it's workload specific (you could say that Ruby's slow anyway, but still, when nearly all of what you do it talking with the database...)