2007-12-08

Why not to learn MySQL?

There Ain't No Such Thing As A Free Lunch.
The price of a product, is only part of it's value. While not paying for the server itself, you end up paying more in development costs, as expected. It all makes perfect sense, and I'm certain everyone reading these words understands this without me explaining the obvious.

What I do intend to mention, are the specific shortcomings and limitations of MySQL, mostly compared to SQL Server - as this is the background I'm coming from.
Let's divide this to development tools, the database itself, and other stuff.

Development Tools
MySQL AB provides: MySQL Administrator, Query Browser and now the new MySQL Workbench.
There is also a myriad of 3rd party development tools for MySQL, but after getting used to the comforts of SQL Server's Management Studio, I can't say that any of them is truly on par with the Microsoft standard of usability. This is a lot better than Oracle's way, which is providing no tools at all except the dreaded SQL*Plus and relying on 3rd party products.
I intend to go in depth about development tools for MySQL in a separate post, so lets leave it at that.

Database Engine
MySQL in many ways is still behind the big commercial databases.
Most notable drawbacks/limitations, which no one would know when first researching about MySQL:
  • Partitioning is nice in theory, in practice it's very basic.
    • Can't move partitions between tables.
    • Can't disconnect partitions from a table.
    • No parallelism in queries over partitioned tables.
  • Stored Procedures are nice in theory, while badly implemented.
    • Can't store comments in Stored Procedures (to be more precise, you can, but when you dump & restore a database - you lose them, so it's a bit pointless).
    • T-SQL "compiler":
      • Requires changing delimiter to be able to load a stored procedure code. To me, this looks like a nasty hack. If SQL Server can do it, so should MySQL.
      • Most of the compilation errors are the "general error - RTFM" type, which point you to the manual - and most of the time doesn't even tell you which row of the stored procedure is the problematic one.
    • No built-in debug ability, unless using development tools that do clever tricks.
    • All the stored procedure code is stored at the "mysql" schema, and not in the correct database.
  • No computed columns support, not even talking about persistent computed columns.
  • Subqueries are nice in theory, while still badly implemented.
    • Most subqueries do just what they're told, and not translated by the optimizer into join-like queries, resulting in poor performance.
  • Explain plans are hard to read and understand - compared the the beautiful explains that the SQL Server tools generate.
  • No parallelism in the query level - each query is one thread.
    • Bad for data warehouses - won't help that big GROUP BY query to have 1 CPU at 100% while the others are idling.
    • Higher latency for heavy queries.
  • Simplified security model
    • No schema vs. database differentiation
    • No groups.
  • Any schema change requires rebuilding entire table - even if it's a simple metadata change. Adding a simple nullable column is as heavyweight as rebuilding the entire table.
  • No online table maintenance - table is locked during index rebuilds (online table maintenance is new in SQL Server 2005 Enterprise, so this is forgivable).
  • Can't build index using sort - adding a new index is slow.
  • Does not support hash joins.
  • Can't detach & attach InnoDB databases between instances (although it is possible with MyISAM)
  • The FEDERATED table type is completely broken as I see it: the code executed on the remote server is horrible, the feature is poorly documented, transactions do not work. Don't expect an efficient "Linked Server" implementation like in SQL Server.
  • InnoDB tables can't be Clustered (or Index Organized in Oracle-speak) by any other than the primary key. So you can't cluster a table by a arbitrary DateTime column, for example.
  • Replication is far from being in the same level of flexibility as SQL Server's.
  • Backup is problematic if you're used to SQL Server
    • Backups by mysqldump are slow and are just a list of SQL statements to recreate the database. Nothing like the binary backup files in SQL Server.
    • No hot backup at all for MyISAM (you can have "warm backup" if you stop the writes and flush data in memory to disk)
    • No built-in hot backup for InnoDB, unless you pay for Innobase's backup solution.
    • Restore is not a binary restore of the database files, but a full reconstruction of the entire database. This is faster than you might think, but still very slow.
    • There's a good explanation of the problems here at the Zmanda Team Blog.
Other
  • Books Online/MSDN is amazingly better than the MySQL online manual. One of MySQL Enterprise's big features is access to a high quality Knowledge Base - 'nuff said.
  • No neat bundled features like Analysis Services, or Reporting Services.
Well, I think this is about it.
This post seemed like a rant when I read it all over again - maybe because those are things I was expecting, and was surprised to be missing/broken/bad in a product which sounded so perfect when reading the "New Features in MySQL 5.1" type executive summaries.

So, consider yourself warned - and learn MySQL anyway, you know you should.

4 comments:

  1. "Backups by mysqldump are slow and are just a list of SQL statements to recreate the database. Nothing like the binary backup files in SQL Server, but very similar to Oracle's dump files."

    WTF? Oracle's export creates compact binary files, not text files with SQL statements.

    ReplyDelete
  2. It does? Well, my bad then. I never was an Oracle expert - but I specifically remember seeing one while I was in the army when one of the Oracle DBAs worked on something. Maybe it was an export and not a dump?
    Anyway, that's comforting to know, thanks :)

    I'll remove the misleading statement.

    ReplyDelete
  3. Oracle started to supply a free development tool about two years ago - Oracle SQL Developer.
    It's not as good as other 3d party tools yet but they are trying.

    http://www.oracle.com/technology/products/database/sql_developer/index.html
    Shlomo, I officialy appoint you the number one MySQL expert in Israel :)

    ReplyDelete
  4. People should read this.

    ReplyDelete