Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

8 MySQL gotchas worth a rant

Peter Wayner | July 23, 2015
MySQL is easy to install, relatively fast, and loaded with features. If that's not enough, it's also one of the most prominent flagships of the open source movement, the big success story that showed us that a winning company could be built around open source code.

Everything breaks. The boss wants the website working in hours. There's no time to re-architect the solution. What does the programmer do? Maybe create some hack that treats the Canadian postal code as a Base64 number and converts it back to base 10? Or set up some secondary table with a special escape code that indicates the real postal code is elsewhere? Who knows? There are dozens of hacks and all of them are dangerous. But you have mere minutes to figure it out.

MySQL's relational rules keep everyone honest and careful, but it can force us to hide our troubles through hacks and dodges.

JOINs

For a time, the idea of breaking data into multiple tables was the greatest notion in computer science. Not only were the resulting tables dramatically smaller, but they enforced simplicity for everyone. But that discipline, and pleasure, comes at a price in the form of JOIN statements.

No part of SQL sends developers into fits of confusion and despair more than trying to build a complex sequence of JOINs. Then the storage engine needs to figure out the optimal way to unpack the JOINs efficiently. The developers build an elaborate query and the database unpacks it.

This is why many developers who want speed give up on this grand experiment and denormalize their tables. Instead of splitting up items, they dump it all into one big, wide table and side-step the complexity. Everything is faster, and the server doesn't run out of memory (as easily).

Disk space is cheap now. There are 8TB drives on the market, and bigger ones are coming. We no longer need to be pulling out what's left of our hair working with JOINs.

Fork confusion

Yes, the existence of a solid, well-supported fork of MySQL brings competition and choice, but it also breeds confusion and chaos. To make matters worse, MariaDB, the fork, is run by Monty Widenius, the same guy who helped make MySQL what it is. Is MariaDB the true sovereign that deserves our allegiance? Or is it MySQL? Should we stick with the central code run by the organization that built the database originally? Or should we join the rebels who are certainly clever and often cooler?

And how do we unpack the message about compatibility? On one hand, we're assured that MariaDB and MySQL are pretty much interchangeable. On the other, we have to believe there are differences -- why else would everyone be sparring? Maybe they're in the range of performance and our queries work the same way in both camps? But maybe they don't -- or won't in the future.

Storage engine chaos

MySQL isn't really one database; it's several, all squirreled away under a surface that hides most of the details. In the beginning, there was MyISAM, an engine that was fast and not particularly careful with consistency. That was OK because sometimes you need speed and can live with inconsistent results.

 

Previous Page  1  2  3  Next Page 

Sign up for CIO Asia eNewsletters.