
MySQL Best Practices - nreece
http://blog.tuvinh.com/top-20-mysql-best-practices/
======
encoderer
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.

------
kennu
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).

~~~
encoderer
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."

~~~
kennu
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.

------
edd
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.

~~~
bad_user
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.

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

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.

------
prodigal_erik
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...

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

