Why do you torment me so?

March 2, 2012 11:09 AM

Is there some killer feature of MySQL that explains its wide use relative to PostgreSQL?

I've been getting back into web development lately, and find that a lot of software is still almost inextricably linked with MySQL. However, I also find that MySQL has an terrible query planner, making simple things like subselects ridiculously slow.

Is there a particular reason people write against MySQL, or is it just done because that's how it's always been?

posted by wierdo to computers & internet (17 answers total)

I don't know, but I can take a guess. If you're only using basic features and MyISAM then MySQL is very fast. I would guess that most free packages out there don't care about subselects et al. but rather just use the DB as a datadump.

I'm stuck using MySQL for things and it drives me nuts because I like using the "advanced" (for lack of a better word) SQL features. Subselects, union, views, foreign keys, and triggers are all things available in MySQL, but not well. I wish I could use CTE. And some days I'd kill for the ability to create indexes on function results.
posted by sbutler at 11:19 AM on March 2, 2012 [1 favorite]

Well, one reason people use MySQL is because that's what's bundled with WAMP, MAMP, etc, when developing on Windows or Mac. It's pretty easy to set up -- download the installer and you've got Apache, PHP, MySQL, all set up and ready to go with no configuration necessary. Then, you've got phpMyAdmin to easily manage your database.

I recently got Postgres running on my Mac for a Rails project and it was way, way more complicated. MAMP takes literally 10 minutes to install; Postgres took half a day. (It does seem to be a better database, though. I ran into transaction issues with MySQL - at least its MyISAM tables - in a previous project, which PostgreSQL handled easily.)

So killer feature for MySQL? Simplicity.
posted by losvedir at 11:21 AM on March 2, 2012 [1 favorite]

I believe it originally became popular due to its low memory requirements. Low memory begat bundling, and bundling is the reason people still use it when memory doesn't matter as much. Also, whoever runs postgresql seems to be clueless about how to make it attractive to run-of-the-mill developers who don't have time to read a 900 page manual.
posted by michaelh at 11:24 AM on March 2, 2012

My hunch is that it's because, for a long time, it was just easier to set up and do basic admin of a MySQL database than Postgres. Any web hacker could modify an .ini file and fire up a web admin to MySQL, but I remember Postgres being this whole shebang you needed to wade through, and there was no GUI, so why bother? And if you're setting up someone's Wordpress blog, you're probably not interested in discussing the finer points of query schedules.

Also, I think MySQL ran on Windows much earlier than Postgres, for whatever that's worth.
posted by mkultra at 11:26 AM on March 2, 2012 [2 favorites]

mkultra has it. Postgres wasn't available for Windows until relatively recently, and it was somewhat more difficult to install than MySQL, even on Unix.
posted by chrchr at 11:33 AM on March 2, 2012

For a very long time, the reason I used MySQL instead of Postgres was that I could install it without recompiling my kernel. I am not kidding.
posted by duien at 11:44 AM on March 2, 2012

What mkultra said. Postgres was ahead of MySQL feature-wise (transaction handling, integrity constraints, etc.) for a long time. MySQL was easier for non-experts.
posted by phrontist at 11:46 AM on March 2, 2012

Postgres evolved from Ingres, it has been around since the 70s . It didn't actually support SQL until around 1996.
posted by Ad hominem at 11:46 AM on March 2, 2012

Intertia mostly. Postgres was and probably still is a pain for newbies to start with. Is there even a myphpadmin equiavlant? Mysql was easy to get started with and later picked up and supported by a large corporation. Oh, and I don't think postgres had replication until recently.
posted by damn dirty ape at 11:48 AM on March 2, 2012

MySQL started out fast, and slowly has become more and more ACID and has supported a larger subset of SQL. Sub-selects used to not even be allowed, which is why they are now slow.

Postgres started ACID and has slowly become faster and faster. But it has always supported all of SQL. Which meant it was really slow in the beginning.

MySQL got used because it was fast, with programmers being confident enough in their programming that they were sure that they could work around any failures of ACIDity. Then it stuck. And it turned out that those programmers were wrong. But now MySQL is everywhere, so people use it because it is the most popular, which drives the whole vicious cycle around and around.
posted by pmb at 11:55 AM on March 2, 2012 [1 favorite]

I recall looking at both Postgres and MySQL way back when. Postgres had a fuller feature set but for use as the database behind a CMS, MySQL was faster and much easier to set up.
posted by tommasz at 12:14 PM on March 2, 2012

Didn't the Postgres project consciously aim for being a platform for database research and development, rather than being optimized for, e.g. being a backend for websites?
posted by thelonius at 12:44 PM on March 2, 2012

mkultra describes my experience and memories exactly. I've only made the switch to Postgres in the past couple of years (I'd been meaning to for a couple years before that) because MySQL was just so easy cross-platform. Now that I've made the switch, I wish I had a long time ago.
posted by yerfatma at 1:14 PM on March 2, 2012

mkultra for the win. Back in the days, MySQL was much easier to setup, and so that's what many went with. Possibly due to this, many hosting services provided it as the database of choice, and unless you are doing something complex, a website that runs against one LAMP stack probably runs about the same against a different one, version numbers aside. Even if you were adept at configuring PostgreSQL, it made more sense to use MySQL at the initial stages of a hobby-level project, since it was reasonable to assume you could find a hosting service that offered what you needed with very little searching. These days, it's probably more about that installed userbase than the database features itself, since most people only use the most basic functionality.

Of course, if you are doing things right, then only your abstraction layer is affected. If you aren't doing anything complex, then it's likely not even much of a change at all, since both are supported by most ORM solutions; however, it doesn't sound like that is necessarily the case in your work.
posted by mysterpigg at 1:25 PM on March 2, 2012

I can't speak to present-day, but as recently as 2008 it was a lot easier to scale & build MySQL clusters than it was under Postgres. Replication under MySQL can be a PITA to maintain, but it worked well.

As a datapoint: A lot of well-known very large sites (*cough* Facebook) use MySQL at redonkulous-scale.
posted by swngnmonk at 3:42 PM on March 2, 2012

mysql was available for windows much earlier, mysql was much easier to support in a shared-hosting environment, and the configuration out of the box is reasonably tuned for decent performance. it had very good integration with php. it snowballed from there. it's a "good enough" solution for a vast number of web problems, and for most of its history, the development of mysql was driven by applications in the web space, which meant it had and developed functionality for solving some common problems in that space.

on the community side, i believe that it was seen as more inviting to people just trying to get a job done and not as tied to academic ideas of how a database should work.
posted by jimw at 9:43 PM on March 2, 2012

Thanks to everyone for the answers. having been a PostgreSQL user since nearly the beginning, I've never had terribly good perspective on MySQL.

FWIW, PostgreSQL 6.x also lacked full SQL support. It didn't even support left joins, though you could emulate them at reasonable speed using unions. It made queries quite verbose, but given that this was pre-InnoDB on the MySQL side it was really the only option for a free as in beer ACID compliant SQL database.
posted by wierdo at 9:49 AM on March 4, 2012