MySQL is a great DBMS. I use it regularly because it is fast, lightweight, portable, and it just works. But it is also a really annoying product at times, demonstrating its grass roots community driven upbringing.
Case in point: MySQL is not a single DBMS engine, it is actually a collection of them, each with pros and cons. The two most used are MyISAM and InnoDB, and when you create a table you can specify ENGINE=MyISAM or ENGINE=InnoDB (or omit ENGINE and the default will be used). So why provide both MyISAM and InnoDB? Well, they support different features. MyISAM is super fast and supports all sorts of useful features, but does not support transactions. InnoDB supports transactions, but not several other features. And an application I am working on now needs both transaction support (and thus InnoDB) as well as full text search support (and thus MyISAM). No problem, a single database can contain tables built for any engine, so some of my tables can use MyISAM and some can use InnoDB. Right? Wrong! While that will work, I’d have to sacrifice DBMS enforced referential integrity because foreign keys cannot span engines! Multiple engine support is all about flexibility, but sometimes flexibility gets in the way. Ugh!
I do like MySQL, really. And many others do too (it is used in over 6,000,000 installations including some pretty major ones like Yahoo). And the features that are being added to MySQL 5 (triggers, stored procedures, views, and more) are important and long overdue. But still, every once in a while MySQL feels like a product built without the benefit of rigid product management, which I guess is exactly what it is.

11 thoughts

  1. Based on your requirements it sounds like PostgreSQL may be worth a look. Though you may want to check on the status of full text search in PostgreSQL.

  2. Good post ben – and an excellent point regarding the 2 engines. I tend to lean toward mySQL when I have a task that requires very fast selects – but not to many transactions. It seems to be lightning on simple to moderately complex select statements.

  3. I remember reading a quote on MySQL.com one time saying that you should put all of your business logic in your app, not the DB. The fact that the MySQL developers adhered to this philosophy is why they took so long to start working on features like stored procs and triggers. Of course, there are many people on both sides of the DB vs. App argument, and personally I don’t think either side is correct. It all depends on the situation. But its an interesting bit if trivia that the MySQL guys have always fallen in the "My DB should be lean and quick, with all the heavy logic processing living in the app" camp.

  4. I tend to lean towards MySQL because all the reasons Ben mentioned, plus one other….cost. Its hard to beat free, especially when you are getting a product like MySQL.
    I ahve not bumped into any of the issues Ben refers to…yet, but its nice to know this in advance.
    BTW – has anyone played with the MySQL 5 Beta?

  5. As far as I know MyISAM tables don’t support referential integrity anyway, even across other MyISAM tables. But I may be wrong and maybe this changed inside newer releases.

  6. What is this application being used for?
    If it is going to be used as an internal application and less than 25 conncurrent connections will be hitting it, I would suggest you look at MSDE from Microsoft.
    http://www.microsoft.com/sql/msde/
    It’s the same exact engine as SQL Server 2000, except that it will only support 25 concurrent connections and the maximum space all databases can contain is 4 gig.
    For client based web applications, this is a perfect database engine. It is also a great development platform. Best of all if you want to purchase SQL Server 2000, you will have no problem attaching the database.
    Less also not forget about ORACLE.
    http://www.oracle.com/technology/software/products/database/oracle10g/index.html
    They also offer their database FREE for download so that you can build your applications.
    With the two strongest and most popular database companies (Microsoft and ORACLE) offering their database platforms for FREE to download and develope on, I don’t see why people still fool around with MySQL or any other database for that matter.
    Almost any web hosting company no adays offers SQL Server as a database for their hosting plans at very reasonable prices.
    I love open source alot, don’t get me wrong, but I love my time more. Alot of the open source software out there is great to fool around with when you have the time to work through the quirks that they have, but when you are on a dead line it’s better to go with something that has a name and you know is going to work.

  7. Scott, I am running the MySQL 5 beta,and so far it is pretty good. I did find one SELECT statement that could crash the server, I reported it and apparently it has been fixed in a newer build. Other than that, it is working beautifully for me.

  8. It’s worth mentioning that InnoDB is not part of MySQL, it’s a separate product, not part of MySQL itself. It’s designed for MySQL, but it’s developed and licenced separately.

  9. Why are you continuing to use MySQL if you need features? Currently PostgreSQL natively run on Microsoft Windows, so I don’t see any valid reason to use MySQL anymore. You can also use the free PostgreSQL Manager http://www.sqlmanager.net/ that is really better than the MS Enterprise Manager.

  10. faser,
    just to clarify, the professional product isn’t free, the lite version is. Even though it is free I still wouldn’t use it because it lacks ALOT of functionality that a DBA would want.
    Although I will say that I wasn’t aware of PostgreSQL haven’t a windows version. Need to check that puppy out. I was always impressed by PostgreSQL but didn’t want to be running Linux to use it.

  11. I think mySQL value as a database will increase significantly once version 5 is out as a stable product. I would like to see a lot of advanced functionality put into mySQL so that it can compete with commercial products not on price alone. Remember, that for a company that actually needs its own DB the product price is not the total price of ownership – please add all the developer work. More advanced features decrease developer work (at least in theory) and thus decrease total product ownership.

Leave a Reply to Massimo Foti Cancel reply