Sucks To You: MySQL

Excuse the AvenueQ reference here, basically horrible as it sounds, this is going to be one of my programming rants. I have a couple of pet programming rants I tend to find myself repeating, this is one of them and in due course I may even put the other rants up here. That way I can just point people to this blog instead of repeat myself yet again.

MySQL is one of the many popular Open Source systems that I hate because it gets way too much credit than it really deserves, not to mention the biased option that people tend to have. On top of this, much like PHP (yet another rant, for another day!), MySQL seems to have a somewhat deluded and brainwashed user base. While this post will come across as somewhat grumpy and scathing towards MySQL, the main reason I wanted to write this was to clear some of the common myths / misconceptions about MySQL that seem to have spread round the internet like some virus to developers.

All in all, I will bold enough to say that MySQL is as evil to SQL as VB is to programming in general.

Encourages Non-ANSI Standards

Firstly, the first thing that annoys me no end about MySQL is while it is generally compatible with ANSI SQL 92 standards, it seems to encourage users to write poor SQL code that is not portable. It would not be a surprise to me if this was some cunning design plan my MySQL’s management as a poor means to building some form of vendor locking through developer ignorance.

For example, the MySQL often encourages developers to use non standard characters such as backticks instead of double quotes for specifying structure names such as field and table names. The other common trick is to use C style string escaping that uses the backslash instead of the using two double quotes like the ANSI SQL standards specify. Unless you manually tell MySQL to use ANSI compatible SQL, it will generate it’s quirky SQL commands when creating dumps, which I guess has encouraged many developers who started databases with MySQL to follow this ill-fated convention.

As a result, there are so many Open Source projects where you run into issues if you want to use SQLite or PostgreSQL because the SQL code they give you for basic DML statements uses the MySQL specific quirks.

Bullcrap Marketing

MySQL only dominates the Open Source DB market because it’s had corporate marketing, which has lead people to believe absolute crap. The reality is that it’s a known fact that PostgreSQL is a far more stable and feature rich RDBMS, and has been for many years compared to MySQL. Yet if you enter put ‘MySQL vs PostgreSQL’ into Google, you’ll be inundated with shoddy reviews which all claim that MySQL is X number of times faster for practically everything.

MySQL Can Be Damn Slow!

From experience, having run into major performance issues with MySQL before, I looked at PostgreSQL. I found the issue with MySQL was that it would only use one index per a table / aliased table, which was making it slow. There was no way to get round this in a graceful manner using MySQL, and due to the requirements of the application I was coding, I couldn’t really adapt it to work round this rather undocumented design flaw of MySQL. Basically, what I needed was MySQL to be able to utilise several indexes per a table because the nature of the query was one where any number of field criteria where being applied in a flexible way. For instance, sometimes I needed just to search by a given location, sometimes it was for a given price range, sometimes it for was for entries modified in a given date range, but on top that any permutation of those criteria could be combined. For instance someone might want to search by location, price range and date range. However MySQL was too dumb to be able to use each of the indexes separately, combine the results using set logic and then get rows based on the result of the set operations. As a result, MySQL was SLOW AS TURD.

PostgreSQL did not have this annoying restriction and thus was much faster. The bottom line is that MySQL is fast for very trivial queries, however for more complex applications you can soon run into serious issues due to it’s design flaws. This annoys the hell out of me because people are dumb enough to assume the trivial benchmark results for simple queries still hold up for their application in the real world, despite the context being completely different. I only came to realise that the indexing system in MySQL was my problem because I quickly picked up how the query optimiser was utilising indexes in both MySQL and PostgreSQL from commands such as EXPLAIN SELECT. Thankfully, I was able to see past the brainwashing that existed on many comparison benchmark pages.

Quirky As Hell & Lacking In Features

OK this is not such an issue as MySQL has been playing catch up as both versions 4, and 5 have managed to start implementing things like transactions, joins, etc. However back in the day of MySQL 3.x, it was pure hell trying to do anything a real database was supposed to do. MySQL was really nothing more than a fancy file storage interface. Even so, MySQL is still playing catch up heavily to PostgreSQL in terms of typical database features that a quality database system should be capable of doing. GIS support is just one area where I realised this.

Licensing

This isn’t a major issue and many other places mention it, but people often forget that MySQL is not as free as it sometimes appears to be. It licensing is far muddier than the plain and simple BSD license that PostgreSQL offers.

Conclusion

While this doesn’t seem the most productive of blog posts, my main point was to highlight how MySQL has got developers into bad habits, and clear up the generally deluded reviews of MySQL that exist all over the internet, rather than sound like a grumpy old sod. If you are still using MySQL, seriously look at PostgreSQL or SQLite as alternatives. While developers are responsible for researching what RDBMS is suitable for their projects, I have to say from personal experience PostgreSQL generally tends to be far better system for larger projects than MySQL. For small projects where you don’t need to scale up in a distributed manner SQLite is great because it’s lightweight and hassle free. As a rule of thumb I try hard to ensure my SQL code is portable and that I minimise using vendor specific elements in order to keep my options open. When I do need to fall back on vendor specific code, I try hard to ensure it’s clearly documented and abstracted away.

Listening to Classic Rock:
Breakfast in America - Supertramp