Why are there so many PHP sites that only provide for MySQL as a database?

Question

I've dabbled with MySQL and personally, I find it vastly inferior to better RDBMSs like Postgres; while I admit it's come a long way and improved, even the latest version to my knowledge does not even support CHECK constraints to verify data integrity (it allows the keyword but doesn't do anything with it).

As someone who is looking at switching away from Microsoft technologies and into open source, I am appalled by the sheer number of PHP-backed applications that will only work with MySQL as the underlying database. A number of these apps are really good and would save a lot of work in development, but the fact they haven't been abstracted to be database agnostic is usually a deal-killer for me and my technical associates.

So I am curious - I understand why MySQL is so popular and why it's almost always used with PHP, but why do so many PHP-backed sites refuse to be properly developed to allow for other databases, but instead force MySQL when there are much better and more "database-like" options out there? I'm getting increasingly frustrated by these apps that I want to use, but they only work with MySQL and I won't bring myself to use it because personally I find Postgres a much better database, and because I personally feel that your database should enforce it's own constraints instead of doing this only at the code level.

I realize MySQL is popular, and it's not a bad system, but I hate when I find a great application and it'll only work when the database is MySQL because the developers used MySQL-specific modules and/or syntax.

Answer 1

We're wanting our cake and eating it too with this question. First, we want database abstraction. Then, we want CHECK constraints in the RDBMS we choose to use behind that abstraction.

Huh? That means we'll neglect to do data checking in the PHP itself, and things will break using databases without the CHECKs. Either that or we WILL implement the checks in PHP to support an abstracted database without CHECKs, doing twice the work.

I think full database abstraction isn't worth the effort, and is mostly a solution in search of a problem.

Answer 2

Most PHP developers develop with PHP because it's free, easy to get going, and powerful. All of the same qualities are shared with MySQL, so it's a natural fit.

That being said, many professional developers create data-abstraction layers that would allow them easy integration with other backends. But most projects don't need those types of things.

Answer 3

IMO the big problem with so many MySQL-only sites is that MySQL doesn't support half the features of a "real" database, so if you need data integrity you're pretty much screwed and will have to write your own software instead of taking advantage of existing solutions, or compromise your application and don't have any real integrity checks at the database level. You end up between a rock and a hard place.

Answer 4

You're right, PostgreSQL has much better support for SQL and other advanced features, so there's a very good case for why PostgreSQL is superior to MySQL.

However, MySQL is so much easier to install and manage for someone who is just getting started, that it gains a lot of adoption relative to PostgreSQL. Simple tasks like configuring a login and giving it specific privileges are very confusing on a PostgreSQL server, compared to MySQL.

Also, there were a few years early on where MySQL offered native binaries for Windows but PostgreSQL did not. You could get it to work under Cygwin, but that's hardly satisfying for a real Windows developer. By the time PostgreSQL did support Windows natively, MySQL had a substantial lead in market share and name recognition.

BTW: http://www.postgresql.org/support/professional_hosting_northamerica

Answer 5

LAMP is an extremely common development stack. Common enough that even people who don't use PHP know what LAMP stands for.

For those who don't know (all 1 of you), LAMP most commonly stands for Linux, Apache, MySQL, and PHP.

Answer 6

Cross-platform support, as long as SQL is concerned, is like a duck.

You know, a duck can walk, can fly and can swim — and does all this equally bad.

It's much better to stick to one platform and develop a well-optimized application, then to try to satisfy everybody, satisfying noboby in fact.

Answer 7

Back in old times there was a huge difference in the ease of use. MySQL was easy to use and very fast for simple task. Back then it didn't provide full ACID, nor triggers, nor subselects, nor procedures. On the other hand you had PostgreSQL (called Postgres back then), which was much slower, complicated to install and mantain, but provided full power of real RDBMS. The thing is, that the web apps didn't really need full power of RDBMS, so MySQL gained huge popularity, while PostgreSQL was used by few.

Ah, one more thing: as of PHP5 SQLite comes embedded. So I expect that pretty soon a lot of new PHP apps that don't really need full blown RDBMS will use SQLite, rather than MySQL.

Answer 8

I think the key point is exactly what you said, "it's almost always used with PHP". By developing for MySQL, they're maximizing their target audience. Yes, it'd be ideal if they developed it to be able to work with multiple databases, but that can be a fair amount of extra work. Lots of these projects just grow from someone's personal project, which was probably not initially designed to be compatible with multiple engines. Once they're pretty far in, it starts to turn into a major job to rewrite the code to support multiple database systems, and there's usually other features/fixes that their users would rather have.

I also greatly prefer pgsql, but I think if you're planning to use other peoples' PHP applications (forums, blogs, etc), it's just a reality that you're probably going to have to run MySQL to support them.

Answer 9

It's mostly the logical end result of the fact that almost all PHP-capable shared hosting services offer MySQL and only MySQL. The extra work to abstract the database is often deemed unnecessary when almost nobody using the application needs it.

Answer 10

I'm sure its the same reason there's so many ASP.NET stuff that only supports MSSQL. Its the traditional database paired with the language just by convention. Plus using/building database independent solutions is hard and one of those things that "you aint gonna need" when most so many other people follow that convention. When its needed its one of those things that can be "page faulted" in.

If you need to get a php app to use another DB, the php is probably open source, perhaps you can do the work yourself.