as the preferred database engine for my web sites.
(price not being one of the reasons)
I currently have around 100 public websites spread over five servers. For ease of management I have started to look them over and standardize their technology.
I had to decide which database engine to use. These are my OWN highly personal opinions and arguments which made me take the decision based on the needs I have. I am not trying to start a database war here :)
But as the title suggests, I have chosen to use MySQL. Here are the 10 top reasons why:
- LIMIT. In a typical webapplication paging is needed and the LIMIT clause is a simple way of just retrieving the needed records from the database. The traffic between
the database and web application is kept at minimum, as well as the memory required for the web app.
- SQL_CALC_FOUND_ROWS. That select modifier works in conjunction with LIMIT and lets you retrieve the total number of rows you would get if we weren't LIMITing. I have a full C# code example
on this at ASP.NET, MySQL and efficiant paging
- memory requirements etc. Let me just say this: MySQL is so lightweight compared to MSSQL. It might not scale as well, it might not handle the same amount of transactions as fast as MSSQL,
but I don't have those requirements. With a download of 22 MB compared to 50 (or even 250 if you want fulltext) MySQL wins this round in my eyes.
- read performance. I agree: when it comes to transaction intense applications I still recommend MSSQL. but for web apps I have chosen MySQL. A web app is typically mostly reading data, not many
updates. In MySQL you can use the MyISAM storage engine, which uses an extremly fast table locking mechanism, allowing for multiple reader/single writer.
- management. Never thought I'd say this: I have learned to master and really like SQL Enterprise manager, I am also beginning to like SQL Server Management Studio, but, for a remote webserver
nothing beats phpMyAdmin against MySQL. On my development box I run SqlYog.
- backup/restore/moving data. MySqldump is a fantastic tool for moving data from one server to another. Spits out CRERATE TABLE/INSERT statements into a textfile.
So genious, Microsoft recently developed their own version, Database Publishing Wizard.
- MySQL is a full version. Since I said I woudn't talk about price I need to compare MySQL against MSSQL 2005 Express. And that version is crippled. Uses max one CPU,
uses max 1 gigabyte (GB) of RAM, 4 GB maximum database size.
- it's widespread. I have done quite a lot work lately against Wordpress. Having my own stuff in the same database as the Wordpress data opens up a lot of possibilities
- fulltext engine. For MySQL it's inside the 22 MB. Nothing to download/install/configure.
- multi OS support. I now have a Linux box in my machine park as well, since Wordpress is needed. So, being able to run MySQL on Linux as well as Windows sure
is a plus for me.
Last - consider I had all my experience in MSSQL (been using and coding against it daily for 10 years = since SQL 6.5), but still decided to go the MySQL road.
Messages for Microsoft:
- make it easy to upgrade an existing SQL 2005 Express installation to include full text engine as well. A 250 MB download is a joke.
- LIMIT and SQL_CALC_FOUND_ROWS. Something like that. Database support for paging is crucial for web apps, tricking it with TOP (and two separate sort /asc/desc) is not comparable. I know it's not SQL standard, but
I don't care. I need the best functionalty for my solutions, not the "most standard".
These are the two most important reasons I "left you" with regards to my web sites.
Messages for MySQL AB:
- do something about your own admin tools. The Query browser for example is horrible to work with. Luckily there are a lot of third party tools.
