Why I find MySQL to be so bad

MySQL is the most used Open Source database engine. I have personally used it for almost 10 years. I worked with versions 3.x, 4.x and 5.x of MySQL, but I also used other database engines, some of them handling the same "data-set size" as MySQL, while others have been smaller databases (mostly Desktop based databases such as SQLite, MS-Access and Paradox).

This is the first part of my complaint about MySQL and why I dislike it.

I've found a few major problems with the way MySQL implement indexes. For example, if ones takes Firebird, PosgreSQL, Access, Paradox, SQLite, MS SQL Server and few other databases, they all save the last value of the index in a file. Usually with sequences, but not necessarily. MySQL does it differently:

1. Save the last inserted id in RAM.
2. If the server just started (reboot/restart the server), it will take the last index from the table. If there are no items in the table, it will assume that the index of the newly-inserted id, will be 1.

The correct way of doing it is to store the index position in a file regardless of the state of the table. If we wish to start from the first possible index (or from the last inserted id), we should perform maintenance on the database and then apply this change (usually part of compressing data inside the database, but some databases might do it differently).

What else ?

Each SELECT statement locks our table for other SELECT statements! I have multi-threaded applications that perform many SELECT statements on the same table but with different queries, on the same connection pool, however I needed to synchronize many of the SELECT statements just because I had deadlocks when performing several simultaneous select on the same table.

A normal lock will perform a write lock on a select (or no lock at all) not a read lock !

Two issues are not enough, right?

Well, every release of MySQL sub-version create new API version and changes, so for 5 and 5.1 you need two API binding. That's not all. On 5.1, each sub version also creates new API changes, so every version (regardless of how small it is) of MySQL has a different API. That's a nightmare for maintaining communication with the database.

On the other hand, Firebird (which is derived from the Interbase 6.1 source code), introduces additional API routines on each major version, but does not touch the working API. If they do change something, they do it on a major version and not on a sub version. So the bindings for Firebird 1.0 works equally as well with Firebitrd 2.1 (at least for me).

I know, you are going to say that I need to be binding to a higher level of API such as DBI (in Perl) or might even ORM (it's even higher the the DBI approach). However, the lower level will still be changed, so it will not solve the problem, it will only make it harder to solve.

When MySQL finally releases new versions of the database (it sometimes takes them years between patches), they do not release a fully working database, but fatal bugs are found soon enough and every new sub version that comes out, will soon enough have patches to fix them. It seems that 2-3 years are not enough and they require additional time to workaround issues. How can you trust a database like that ? How can you make absolutely sure that your data will not be destroyed by a bug on every new update ?

OK, so you probably think that backups are in order. Well, the official MySQL backup system is part of the SQL language, but restoring the data from the way it saves to backup does not always works. Even table structure are not always saved properly, not to mention the data itself.

The mysqldump is the most used tool for that job. However, if you wish to store binary blob data (not recommended in any database, by the way), this tool will not be a good choice, unless you can somehow use MIME or a different way to place the data of your blob as text and revert it to binary when dumping the data back again.

These are not all of the problems of exporting our database. If we have foreign keys, we must tell MySQL not to check their existence in order to import the data. MySQL can't handle foreign keys in import and export of data properly according to the official documentation (and practice).

If you wish to save and restore BLOB data as MIME (for example), you cannot really do it automatically. Unlike Firebird/PosgreSQL that can be extended by 3rd party tools (see User-defined Functions), making the database work differently from what it was originally designed, so we can get more from our data, MySQL cannot. You cannot extend the database, only write a tool to do it for you (importing complex data).

An additional problem with MySQL is the lack of creating new data types (also known as domain), or supporting more data types. For example in Firebird I can create a new data type. We can take a "varchar" data type in the length of 10 chars, and make a new data type out of it. Now every time we'll need a "varchar(10)", we'll use that data type in the database. Firebird knows it's an alias for varchar(10), but we do not need to write it every time. So it saves us time, and make our data type better in understanding what it does,  But then again, we're asking too much from MySQL I guess.

So what does MySQL support ? how about "DROP DATABASE"? Well sure it has support for it, but:

1. You must remove any Foreign keys on your own, or it might have problems with the execution of the command.
2. You might be required to continue the work of "DROP DATABASE" by hand (according to the docs) because it might not remove everything.

Wow! MySQL is such a reliable engine, that it does not want to remove your data even if you ask it to do so…

Now when MySQL finally works like we want it to, we might find ourselves in the following situation: when we use a bigger data length/size than we are able to store, it just truncates it, and not always the way we will expect.

Let's see:

CREATE TABLE test1(name VARCHAR(4), value TINYINT(2));

INSERT INTO test1 (name, value) VALUES('Firebird SQL', 1996);

The result is as follows:

SELECT * FROM test1;
+--------------+
| name | value |
+------+-------+
| Fire | 127 |
+------+-------+

As you can see, I can actually find that name was truncated (I hope), but the value contains the roof of what tinyint can be. And it only gets worse from here on. Data types such as sets actually can place empty data instead of the actual content, and it can be even worse.

The thing I'm expecting is that MySQL will report an error or something regarding the value that I told it to add, but no, it just add it and does not say anything. I believe that on data manipulation, the database is the tool that should do it rather then my code (it was designed to work better with data manipulation), but I guess my point of view is wrong. So I just need to add extra code to handle the data itself before it is stored. (And I'm not talking about filtering bad chars, for that I have binding support and other strategic ways to store/use that data.)

You think that's all right ? well, wrong !

I do not want to even talk about problems with JOINS that have existed for many years and versions in MySQL, because that requires a post of its own, but what about optimizing our SELECT statements?

Let's see:

Development is ongoing, so no optimization tip is reliable for the long term.

Well I guess it just can't handle this. I wonder if many instances of the Slashdot/Digg effect are due to lack of proper query optimization. Even though there are many other factors to take in consideration (web servers, processes that are inside the server that are running, memory, load balancing [if it exists] etc.), but at the bottom line I guess is that MySQL is not so good for production if you actually need to optimize it.

That's not all I can say, but this post is getting way too long. Thus, more issues that I have with MySQL will be written in post #2.

Right is right, even if everyone is against it; and wrong is wrong, even if everyone is for it

William Penn