Top 10 MySQL Best Practices

So, O’Reilly’s ONLamp.com has published the “Top 10 MySQL Best Practices” at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.
For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.
They perpetuate a myth in #4, “Don’t store binary data in MySQL.” What they really mean is “don’t store large data in MySQL”, which they go into in the tip. While it’s true that there is very little benefit to having binary data in a database, they don’t go into what those benefits are. This means that people can’t make informed decisions, just “the best practice is this so I’m doing it.”
The benefit of putting binary data in MySQL is to be able to associate metadata and other data. For instance, “user 200 owns file 483″. If user 200 is gone from the system, how can you make sure file 483 is as well? There’s no referential integrity unless it’s in the database. While it’s true that in most cases people would rather sacrifice the referential integrity for things like faster database backups and easier partitioning of large data objects, I believe in giving people full disclosure so they can make their own informed decision.
#5 is my biggest pet peeve. “Stick to ANSI SQL,” with the goal being to be able to migrate to a different platform without having to rewrite the code. Does anyone tell Oracle folks not to use pl/sql like collections? Nobody says “SQL is a declarative language, pl/sql is procedural therefore you should never use it”. How about SQL Server folks not to use transact-sql statements like WAITFOR? MATCH… AGAINST is not standard SQL, so I should never use it?
Now, of course, if you’re selling a product to be run on different database platforms, then sure, you want to be platform agnostic. But you’d know that from the start. And if you have to migrate platforms you’re going to have to do lots of work anyway, because there are third-party additions to all the software any way.
And why would *anyone* choose a specific database, and then *not* use those features? I think that it’s a good tip to stick to ANSI SQL if you *know* you want to, or if you have no idea about the DBMS you’re using.
If you want to see how this cripples MySQL, check out Visibone’s SQL chart at: http://www.visibone.com/sql/chart_1200.jpg — you can buy it here: http://www.visibone.com/sql/. If the author of this tip list gets a say, we wouldn’t use anything that’s blue. The chart is *mostly* blue!!!
Along those lines, tip #6 is not to use AUTOINCREMENT. The viewpoint is completely valid, and explains the shortcomings. However, it does not go into the fact that building your own sequences is complex, and they recommend a coded solution to it. MySQL was blasted for years because they told folks “just code referential integrity, the database doesn’t need it” and now people are taking something that’s complex like sequences and saying “just code it”???????
I would amend #6 by explaining how the industry standard is to use sequences, as well as data tables such as calendar tables, and that stored procedures should be built to deal with sequences. The problem with a coded solution is that it is difficult do the proper locking needed for sequences if more than one access is needed at a time. The reason I say stored procedures is that I believe that the database should handle the locking of tables, not the code, just as I believe referential integrity should be enforced at the database level.
Everything else is pretty good. I had a list a while back of “SQL Best Practices” here: http://sheeri.com/archives/104. I may post later on about my own personal MySQL Best Practices….