I was looking on Wikipedia at the differences between Oracle (what I use at work), MySQL (what the open source world primarily uses ), and finally PostgreSQL (what I use at home).
After looking at the first link above and comparing all the features and such, I can’t figure out why everyone uses MySQL or (if they go that route I’d prefer they use) MariaDB. Looking at the chart PostgreSQL seems to be much more feature rich and according to Wikipedia even better meets the SQL Standard.
Having used Oracle so much I can easily see why I like PostgreSQL better. Its much more similar to Oracle than MySQL is.
That still leaves me with: Why did MySQL become the open source favorite? And why does it remain so?
I believe it is because install base. There have been many adoptions and howtos written. MySQL have been seen as more user friendly and LAMP early became a concept. I agree in your assessment. People don’t change habits easily.
Basically it was not about features but ease of use. Earlier Versions of PostgreSQL were neither simple to deploy or configure. At that point MySQL already had GUI Tools (Browser and Desktop) which made it the winner since most people just needed very simple databases for their blog etc.
When you are not a DB expert and all you know is MySQL with all its workarounds and crutches, you just run with it.
I have to agree about the setup issues. Getting MySQL up and running versus PostgreSQL has historically been a win for MySQL in my experience. I think this is changing though. The last time I wrote instructions on getting PostgreSQL installed it only took three steps to get it up.
http://steveno.wordpress.com/2011/12/31/postgresql-setup-fedora-16/
It’s very simple to set up and worked really well with php right from the start. There was a lot of information out there on how to set it up. And it was also pretty fast with myisam though that is missing transaction support. They finally switched to innodb per default though.
That’s why the LAMP stack became so successful.
No one ever said mysql is the best database
BTW, my take is at http://ledgersmbdev.blogspot.com/2012/09/or-modelling-interlude-postgresql-vs.html. MySQL and PostgreSQL appear more similar than they are. I have been working with PostgreSQL since 6.5 and stopped using MySQL for the most part around the time Pg 7.3 shipped.
The basic issue is that MySQL began in the space that NoSQL basically is in right now, namely light, fast dbs offering none of the guarantees you expect from an rdbms. At the time PostgreSQL wasn’t very mature, and had some issues, Oracle was a royal PITA, and the like. MySQL initially scored ease of use points and the fact that you could reasonably reliably prototype dbs on it which you couldn’t with PostgreSQL and Oracle at the time (no ALTER TABLE DROP COLUMN equivalent).
The two have moved in very different directions. PostgreSQL is moving at Oracle’s primary market, being an way to manage information where many applications may need to read/write the same relations. MySQL is moving in the area of offering something somewhere between a traditional RDBMS and a NoSQL db, continuing to offer fewer guarantees (since apps can set sql_mode) where only one app manages a given set of tables and all access goes through that app.
I am in the process of switching from mysql to postgres. I am loving postgres and the learning curve is small. I read on the php.net website that mysql_connect is going to be depreciated as of php 5.5 and I have a lot of old php scripts connecting that way. Since it was time to upgrade them and I had to make a code change anyway… I decided it was a perfect time to ditch mysql for Postgres. I was even able to get postgres on my cpanel with phppgadmin. Very similar to phpmyadmin. Again… happy as a lark!