Many of these are debatable, obviously.

But the only issue with mentioning is that I think it's a much better idea to send a developer that needs help with MySQL to, eg, the MySQL Performance Blog.

Relational theory is hard stuff and I think a developer would be better served to understand a little bit about it than to just follow bullet points like those listed here.

For example, indexing search fields. Could be a great suggestion. Could be a poor one. That depends on a lot of things.

For example, not using natural keys. Same as above.

For example, Enum over Varchar. Same story again.

A lot of these are 100% harmless if your table has 1000 rows. But, honestly, if your table has 1000 rows you'll get good performance from MySQL no matter what you do.

But take the Enum suggestion. Seems safe, right? But adding a value to an enum field forces a table rebuild. That's a blocking operation. And it could take quite a while on a big table.

Also, it will change the way your data is sorted. Enums are sorted by their position in the enumeration, not alphabetically.

So on, So Forth.

The guys at percona and MySql performance blog are super informative. Spend some time there. You'll learn tons.

And we've been using their storage engine in production on a write-intensive system for a year now and love it. Check it out.

The "Store IP addresses as UNSIGNED INT" hint is a really bad idea. The application will break on IPv6 connected servers. The same applies to storing IP addresses in a very short field like VARCHAR(16). It's better to just use VARCHAR(254).

Your advice reads like a suggestion to suffer an actual problem today -- finding and sorting IPs without needing a UDF -- to prevent a possible future problem.

I wrote a book in another comment above, so I agree that the list suffers from a lack of nuance. But I'd say calling this a "really bad idea" should just be replaced with saying "use some caution."

My advice was selfish. I run a personal server that is IPv6 connected, and sometimes web frameworks will break because the author tried to store e.g. REMOTE_ADDR in a VARCHAR(16) column.

So I would like to promote the fact that IP address != 32 bits, and if you really want to optimize for IPv4 address lookup, your code needs to handle it as a separate, specialized case.

Point 6 is a really cool way picking a random row and has never occurred to me. It does make me think though why has something like this never been optimised out by MySQL before running the query. After all it is such a common requirement.

It doesn't seem to me as a common requirement.

"ORDER BY RAND()" means exactly as it says ... fetch the result-set in a random order, and it applies to the whole result-set, (probably) having the same performance characteristics as any other unoptimized "ORDER BY", which is executed before "LIMIT" (I like it because it's consistent).

Many people are using "ORDER BY RAND()" to fetch multiple rows, not just one ... which is also pretty bad. But in such a case other optimizations are in order ... if you're selecting 30 random items, you wouldn't want to issue 30 queries to the database.

I wrote this article and it was stolen from: http://net.tutsplus.com/tutorials/other/top-20-mysql-best-pr...

I like all the constructive criticism. It helps me learn. Also keep in mind that the target audience of this article is very different than for example MySQL Performance Blog. It is mostly beginner to intermediate web developers.

If I wanted to talk about all the exceptions to the rules, it would turn into a book rather than an article.

Strict mode should top the list. By default, MySQL hates your data and wants to quietly mangle it.

In fact ENUM is an especially bad idea without strict mode, because it converts any typo to the empty string, which is almost certainly not what you meant to store. This also happens behind your back whenever the database doesn't have the correct version of the schema...

No. 7 has an unfortunate but funny typo ("Avoid SELECT" instead of "Avoid SELECT *").