The Old Joel on Software Forum: Part 3 (of 5)

MySQL Gotchas

http://sql-info.de/mysql/gotchas.html

After working with MySQL for about 4 years now, I stumbled upon this url above in a slashdot discussion. Absolutely amazing. I still think MySQL is good for certain things, but I sure am going to look at other databases a bit more closely before I use MySQL in another project.

This isn't a "oh my gosh, there is problems with my database! port immediately!" thing, but more of a "I've been thinking of starting new projects in postgresql for a while, and that NULL thing just puts me over the top" type thing.

Just thought others would like to see this.

Andrew Hurst
Wednesday, November 19, 2003

I read it. I use MySQL. MySQL sucks. I already knew that; and I have all my experience using it to back that up. At least for now, MySQL is here to stay.

I might evaluate Postgres for the next version of our product. The limitations of MySQL's SQL language is insane. I find myself writing all sorts of contorted SQL queries.

Performance is, however, a huge concern. More then anything, I need to pull records out very fast. MySQL is designed for that purpose above all else. Postgres is a more well-rounded package and thus suffers a bit in that regard.

Lastly, there is the issue of installed base. Everybody has heard of MySQL and it's installed just about everywhere. It's got lots of history. Postgres is more of unknown quantity; less people have heard of it or used it and it's not as installed as MySQL.

Oh well.. still have a few months before I have to make any decisions.

Almost Anonymous
Wednesday, November 19, 2003

Almost Anonymous,

If you are committed to open source databases, I suggest you try Firebird. It has all the SQL capabilities you seem to expect, is well supported by a small-but-vibrant community, and works REALLY well.

The only area of criticism I have is with the poor state of ODBC drivers for the product, but I noticed a new driver release on their site last week and haven't had a chance to check it out.

HeWhoMustBeConfused
Wednesday, November 19, 2003

I use mysql alot too.

If you stay within certain boundaries, it kicks ass. But one needs to know these limits beforehand.

It has been overhyped, which is what creates the problem. Some people seem to think it covers all aspects and is a state of the art RDBM.

Eric DeBois
Wednesday, November 19, 2003

I have to agree with Eric. It's a great tool within it's limits. For the bulk of what I do, those limits are fine. I've never run into a database that doesn't have gotchas, and it's always a good idea to test things out before committing yourself.

There are definitely alternatives to look at though. I'm the sort of perverse SOB who likes things like Berkeley DB because it reduces external dependencies. SQLite is nice too, for really small projects without a lot of inserts.

My point: MySQL is often chosen as the default tool in the UNIX world, but there are other tools that might be more appropriate for your application. It never hurts to know about more solutions.

Clay Dowling
Wednesday, November 19, 2003

At just pulling records out of a table, how much faster is MySQL that Postgres?

Matthew Lock
Wednesday, November 19, 2003

>>SQLite is nice too, for really small projects without a lot of inserts.

If you're doing lots of inserts at one time in SQLite, make sure to wrap them all in a single transaction (or at least put a bunch in each transaction). If you don't explicitly use transactions, SQLite will wrap each insert in its own transaction - and all the disk syncing really slows it down.

RocketJeff
Wednesday, November 19, 2003

Thank you so much for this list/link. This will prove quite useful to me.

1/4 Ain't Bad
Thursday, November 20, 2003

Without denying that these are issues for MySQL, isn't it the case that all databases -- even the really big boys -- have various annoying dark and dangerous corners to watch out for?

To take an example, if I remember correctly (and I am not a hardcore db guy), Oracle does not permit you to distinguish between NULL values and empty strings ('') in a varchar2 field. To my mind that's a humongous oversight. Any db gurus want to convince me that's a useful feature rather than a design flaw?

John C.
Thursday, November 20, 2003

John C,

I just checked and you are correct for both Oracle 8i and 9i:
SELECT NVL( '', 'this should not show up' ) FROM dual;

result:
'this should not show up'

MS SQL Server and Sybase ASE also suffer from the bizarre handling of null values. Because marking a column as 'null' in SQL Server/ASE results in a variable-length column (for chars) you no longer can insert blank chars in null-able columns.

If you do:
INSERT INTO mytable (null_col, not_null_col ) VALUES( '', '' ) -- two blank chars

It's changed to:
INSERT INTO mytable VALUES( ' ', '' ) -- one space and one blank

I don't know if the latest version of SQL Server still does this.

By way of Oracle's null <-> '' it effectively does the same thing ('blank' chars are really a single space ' ').

These side-effects are a prime example of why nulls are a bad thing.

MR
Thursday, November 20, 2003

I've been a PostgreSQL user since the late 6.x versions. The current 7.4 release (as of this week) is looking very good and my 7.3 installs have been rock solid ever since I've started using them.

I haven't tried Firebird, SAP DB or SQLite because I've been satisfied with PG. I still use MySQL occasionally for simple lookup databases that are read-only. It's useful for things like Zip code lookup databases.

That's not to say there are no bugs or "gotchas" with PostgreSQL, just that none of them are that bad. In my mind, the worst ones are:

64-bit integer fields don't use index if you feed them a 32-bit literal, but if you quote the literal as a string, the index will be used after the implicit cast.

Sequence Generators are 32-bit.

Working with dates can sometimes be weird. Mostly for date ranges.

Tuning the query plan analyzer is not always as straightforward as it should be and on complex queries, it may prove to be necessary to discourage PostgreSQL from using sequential scans because actually tuning the QPA to prefer the proper path may harm other queries' performance.

Inserts outside of transactions can be slow.

Trigger performances in 7.3 is not all that great.

Subqueries used with "IN" and "NOT IN" are slow in 7.3, better in 7.4.

That being said, I'll take a feature that's slow over a feature that's missing or that behaves weirdly.

Saruman
Thursday, November 20, 2003

Oh, and the fact that I have to use the stupid pseudo-table (dual) is incredibly annoying as well.

In virtually every other DBMS I can do
SELECT getdate()

but in Oracle I have to tack on FROM dual; (ignoring the getdate()/sysdate conversion).

BUT -- we can't simply wring our ands and go "This sucks but let's accept it". The more the end-users complain the higher the likelihood that things will get fixed. After all, it’s the squeaky wheel that gets the grease.

A prime example is MySQL’s about-face on foreign keys. Many remember the comment in their documentation on lack of FK support:
(paraphrased) “[FKs] ... are annoying at best and should really be handled in the application.”

The DBMS builds <I>value</I> (e.g. asserts new facts) from data ONLY if you can be assured the data is correct. Handling FKs in your application does NOT assure that your data is correct (as the application is easily circumvented) – the various data anomalies that one encounters when working with a DBMS that is not properly validated in the DBMS can cause data to be virtually worthless (and makes someone spend countless hours fixing it).

After browbeating MySQL with this they *eventually* dropped their condescending (and wrong) statement from the docs and half-assed support of FKs by including the InnoDB table-type.

The problem, though, is that practitioners rarely know what they’re missing so how can you know that what you’re doing is wrong? I often hear ‘right tool for the job’ when defending sub-standard tools (as an aside this worn-out phrase should be stricken from our vocab). The problem with it is that if you don’t know the fundamentals (e.g. why you need constraints) how can you even begin to claim that it’s the ‘right’ tool?

Sort of on topic:
A fun book that, somewhat indirectly, illustrates a lot of gotchas is: “SQL Performance Tuning” by Peter Gulutzan and Trudy Pelzer (ISBN: 0-201-79169-2 for your bookstore searches). For anyone that works with different DBMS products (this book covers InterBase, SQL Server, MySQL, Oracle, and Sybase ASE; not sure why it doesn’t cover DB2) reading this book is a requirement.

MR
Thursday, November 20, 2003

MR,

>Oh, and the fact that I have to use the stupid pseudo->table (dual) is incredibly annoying as well.

What is it you have against syntactically correct SQL statements all of a sudden? :-) SELECTs without a proper FROM clause would require hacking the SQL syntax parser, to handle the special case of functions.

Also, you dont have to use SÈLECT FROM DUAL to run stored functions, you can select them from any table, along with other colums, but you probably knew that already.

but in Oracle I have to tack on FROM dual;

Patrik
Thursday, November 20, 2003

Patrik,

Other DBMS products don't have a special case for functions.

In MS SQL/Sybase ASE I can do:
SELECT 1
GO

Or in MySQL I can do:
SELECT 1;

Just wishing Oracle didn't require me to tack on the useless 'from dual'.

MR
Thursday, November 20, 2003

So you're complainging about having to type the extra characters "FROM DUAL" instead of the much simpler "GO"?

Wow. Talk about a nitpick. I mean, DUAL is kinda a stupid name for it, but it takes like 5 seconds to understand its use -- it's a table gauranteed to have only one element.

I mean, SELECT function() makes no sense. If you're going to bastardize the SQL syntax, why not just allow "function();" instead?

Richard Ponton
Thursday, November 20, 2003

In standard SQL, as Patrik and Richard pointed out, SELECT without a FROM is invalid.

You can use the VALUES() table constructor without a SELECT or FROM though, e.g. VALUES(CURRENT_DATE).

At least that should be the case if your DBMS claims entry level SQL/92.

ThinkSQL
Thursday, November 20, 2003

I think I raised more important issues than the stupid 'FROM dual' thing, but I guess if that's all you want to pay attention to... :rolls eyes:

MR
Friday, November 21, 2003

On a semi unrelated topic, I really, really like MySQL's "--i-am-a-dummy" switch.

Leonardo Herrera
Friday, November 21, 2003