Xaprb

I’ll be helping Morgan Tocker deliver the second half of his training course for MySQL Developers/DBAs in New York City in a few days (more Percona training). It was a snap decision at the last minute, but I’m hoping I’ll still get to meet some folks there. If we’ve corresponded over email or blog comments and you would like to get together, ping me in the comments here!

If you’re in the New York City area and you use MySQL, you should consider attending this course, too. Morgan knows his stuff and has written a good curriculum. Attendees give his courses excellent feedback, and the price is very reasonable. Oh, and I’ll be there too, did I mention that? You can pick my brain, no extra charge. Bring organic free-trade chocolate to assist with extra-tough questions.

The headline is flame-bait, don’t take it. I just wanted to point something out about character sets and collations in MySQL.

To the uninitiated, it may seem overwhelming. Everything has a character set! Everything has a collation! And they act weirdly! The server has one. The database has one (oh, and it changes magically as I USE different databases.) Every table has one, and columns too. Is that all? NO! My connection has one! Kill me now!

Relax. In truth, only one kind of thing actually has a charset/collation. That is values. And values are stored in columns. The only thing that really has a charset/collation is a column.[1]

What about all the rest of those things — connection, database, server, table? Those are just defaults, which determine what charset/collation a value gets if it isn’t overridden. So if the table’s default charset is utf8, and you add a column without saying what it should be — why, it’ll be utf8. If the database’s default is latin1 and you add a table without saying what its default should be, it’ll be latin1.

[1] It’s not quite true. Literal values in SQL statements are values too, as are @user_variables. But if your connection’s charset is latin1 and you say SELECT "xaprb", without an explicit introducer, you’re really saying SELECT _latin1 "xaprb". Again, the only thing that really has a charset/collation is a value. Other things are just defaults.

Cary Millsap has a concise, readable paper on performance. Anyone involved in database performance optimization should read it. Cary’s writing has heavily influenced the mk-query-digest tool for analyzing MySQL/PostgreSQL/Memcached/HTTP query performance, and I think you’ll get a lot more from mk-query-digest if you read this paper — and you should also read his book, reviewed here. It’s one of the top books on my Essential Books List.