Posted on

Issues With MySQL

If I had my choice I’d ALWAYS choose PostgreSQL over MySQL every time. It is a stronger, more reliable, more robust database engine without question. So why do I even think about MySQL, ever? Because I have to. It is my job to know technology and, unfortunately for the industry at large, MySQL is the defacto standard for nearly every open source web application in existence. The very predominance of MySQL does not make it better, just a necessary evil that we have to deal with every day. Unfortunately the pure power of numbers often forces us to create new systems in MySQL just for the sake of simplicity on our client systems.

The Complaints List

So why do I dislike MySQL so much? There are a number of reasons. Granted, MySQL has done a good job in the past 2 years of finally putting decades-old technology in play. Things like stored procedures, though still quite a bit wonky in it’s early iteration, is not longer a “can’t do it” mark against MySQL. Same goes for views.

However, there are still many nagging issues out there that get to me every time I dig into MySQL.  Here is my short list as of the v5.1 release:

  • Want full text search? Can’t have foreign keys!
    This is because the MyISAM engine supports full text search but NOT foreign keys.
  • Want foreign keys?  Can’t have full text search!
    Same thing as above, but in reverse.   InnoDB supports foreign keys (hey, who needs ’em it’s only a relational DB we want) but alas does not provide any method for full text search.
  • Want foreign keys?  Can’t have static length records.
    Another give & take between MyISAM and InnoDB.  MyISAM provides for static length records, which provide MUCH faster access times in large lookup sets.   InnoDB does not have this.  Unfortunately the heaviest use of lookup tables happens to be in relational databases which by their definition require the foriegn keys of InnoDB.
  • Default NOW()
    This staple of many database engines like Oracle and PostgreSQL allows you to set datetime fields to a default that sets the time automatically for when a record was updated or inserted. Not available in MySQL  Yes,  you can use TIMESTAMP but you are only allowed ONE default CURENT_TIMESTAMP per table.  So much for created/modified date + time fields.
  • Default <any function here>
    Expanding on the above concept… you can’t use ANY function as a default value.  Yeah, it’s difficult to code in an DB engine, but not impossible.  Nearly all the other engines allow for this.  That means no default USER() either.
  • Stored Procedure Delimiters
    While stored procedures are a welcome addition, why do we need to redefined the standard end-of-command delimeter when creating the functions?   Every other DB engine is smart enough to know you are in a stored procedure block and allow for nested semi-colons.  Not MySQL, you need to change your delimiter to something funky that you’ll never use like maybe // and then remember to change it back when you’re done or all hell breaks lose.
  • Default Engine = MyISAM
    Yes, it is faster.  Yes, it has full text search.  BUT it is less stable than InnoDB AND it does not support foreign keys.  A properly designed database application should really be using foreign keys.   It ensures data integrity at the database v. application level and until the advent of stored procedures in MysQL was a good way to get deletes and updates to cascade.   Yes, you can change the deault, but shouldn’t a good database be relational and inherintly more stable by default?  How about making speed, instability, and lack of relations secondary?   Even more fun is the fact that if you did create a table with the default MyISAM engine and then need to refer to it via a foreign key in InnoDB, you need to conver the entire table.  Hope there’s not much data in there!

Summary

While there are some good features behind MySQL, most notably the strong online community and user documentation, for true heavy lifting I’ll always argue that PostgreSQL can do the same thing better 99% of the time. Until I can convince enough people to make the conversion, I’ll keep adding to my “dislike list” for MySQL.