MySQL has gotten better as time has gone on. I want to make that clear up front before I bash a handful of things about its current state. It has gone from being little more than an SQL front end to flat files to being almost a real database (if you use InnoDB and friends).
- No full outer join; this one irritates me to no end when working on diff-type queries (ones that, like the diff utility in UNIX, take a set of rows and compare them against another set, getting a difference) because I have to union three queries together, rather than simply one query with a full outer join.
- Constraints and concurrency are not enforced by default. You have to setup InnoDB to make it work properly. There is simply no excuse for not maintaining relationships in a RELATIONAL DATABASE. InnoDB is great, don’t get me wrong, but I should not have to setup an add-on (and to run properly, you will need to configure the engine, at least a little) to get something so basic and fundamental. With MySQL 6.0, Sun has promised an end to this with Falcon, but that has yet to happen. 6.0 isn’t out yet, and it wouldn’t be fit for production use for a while longer even if it were.
- Stored procedures. These were not added until version 5.0 of MySQL (many shops and shared hosts are still on 4!), but now they are here–sort of. The fact of the matter is that stored procedures really aren’t usable in MySQL. The syntax is clumsy, requiring messing with delimiters to even create them. They do not work from the command line because of this, which makes testing harder. In addition, the syntax is lacking quite a bit featurewise. The easiest example of this is also what should be simplest: how do you iterate over a cursor? Simple 101 feature, right? Not really. At least, not in MySQL.
Those are the biggest things I can think of off the top of my head. I’ve got a hunch that I would not be happy about replication or binary logging either if I had the time to set them up. Now for any of you reading this (if anyone does read this), you may ask: why not just use PostgreSQL? Or Oracle? Or even Microsoft SQL Server? The shop I am working in will not invest the sums for Oracle or MS SQL Server, so those are out. The current reality is that we will not be leaving MySQL any time soon.
Tweet This Post 
Delicious 
Digg This Post 
Facebook 
Reddit 
Stumble This Post
and the fact that tables with ibdata aren’t shrinkable after a delete ??
I got one. If you have a table with two TIMESTAMP columns, and update one of them, both will be updated. This is because MySQL didn’t have triggers for a very long time, and thus created a data type that updates every time you change a row.
Last I checked, even innodb didn’t honor CHECK constraints.
Fourth: no easy way for raising exception
Fifth: no easy logging
What can I say, I agree with you… MySQL with default MyISAM engine is only pseudo relational database. There are a lot of other limitations in MyISAM, e.g. transactions or table locks.
PostgreSQL is much more powerfull open-source database system. If I can, I always choose this RDBMS. Maybe PostgreSQL is a little bit slower for smaller databases, but for medium or large…
The lack of full support to full outer join is not a pain
http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
Constraints and concurrency are not enforced by default: I love actually. By moving the logic of maintaining relationships into application developers’ responsibility, applications can be made more flexible and user-friendly. At least error message will be more friendly.
Stored procedures: It is overrated. SP can be a maintenance nightmare, especially when you do heavy refactoring
@pcdinh: “Constraints and concurrency are not enforced by default: I love actually. By moving the logic of maintaining relationships into application developers’ responsibility, applications can be made more flexible and user-friendly.”
Until one of the app devs “forgets” to validate, or does not understand the data relationships, resulting in inconsistent data. What happens to user-friendliness then? Fail.
A strength of a properly designed _relational_ database is the management of relationships in a single location, without exception. Use the right tool for the task.
Secondarily, unfriendly error messages are a failure of application developers who do not know how to properly detect or catch exceptions and handle them properly.
@Jonathan – right you are; I knew I overlooked a thing or two
@pcdinh:
I really can’t say that I agree on constraints. Stored procedures I can see as a matter of taste, especially if one is using prepared statements, but not constraints. I have seen too much code where the constraints were not being enforced (either in MySQL or that they had never been created in the first place) and things broke. Rules that should have helped to ensure valid data were not being enforced and it caused a big mess. You may be able to somewhat get away with it when you’re just building a website (after all, an orphaned article is an unfortunate bug, but not a catastrophe), but it is potentially huge when working with actual software.