If you've ever created foreign keys on an InnoDB table, you'll see it automatically creates indexes, if none exists, on the referenced columns in the parent table, and also in the foreign key columns in the child table. This article explains why both are needed.

This article explains how I replaced file-based methods to ensure only one running instance of a program with MySQL's GET_LOCK function. The result is mutual exclusivity that works in a distributed environment, and it's dead simple to implement.

I've just improved innotop substantially, and released version 0.1.106 (download innotop from the original article), and I'm also preparing a series of articles on how to use it for real, practical things. I'd like to know what you think of it, what problems you have, what features you want. It would be a huge help if you'd start it, toggle through its modes, and give me your feedback.

MySQL doesn't yet provide good tools for some troubleshooting tasks. Fortunately, there is some low-hanging fruit you can pluck. One example is a tool to record who owns a MySQL database connection, so long-running transactions can be traced back to the source. This article demonstrates an easy way to solve that problem.

At both my current and previous employer I've been involved in designing and maintaining aggregate, or "rollup" tables for advertising traffic data. I have learned several methods of propagating changes from the atomic data through to its aggregations. This article discusses these methods, how to implement them, and their pros and cons.

MySQL on GNU/Linux appears to be able to either run multiple processes, or one process and multiple threads. We've noticed a significant CPU penalty for multiple processes, probably from the context switching overhead. The trouble was, one of our servers wouldn't use threads; it wanted to use multiple processes. This article explains how we got it to use threads instead.

"My code is all in version control," Joe said proudly. "Everything is versioned. I'll never lose any work." But then he lost some "other" code he didn't realize was critical. This article is about how to find and safeguard all the hidden code you don't know your business relies on.

SQL blind inserts are a common mistake, but they're easily avoided. This article explains what blind inserts are and how to avoid them, as well as dispelling a common misconception about how to avoid them.

I've written a lot recently about MySQL index and table structure, primary keys, surrogate keys, and related optimizations. In this article I'll explain how MySQL's index structures enable an extremely important query optimization, and how that differs between storage engines. I'll also show you how to know and predict when the optimization is triggered, how to design tables and queries so it'll be used, and how to avoid defeating it with poor practices. Plus, I'll peek a bit into InnoDB internals to show you what's going on behind the scenes.

MySQL and InnoDB expose lots of information about their internals, but it's hard to gather it all into one place and make sense of it. I've written a tool to do that, and you are free to download and use it. This article introduces innotop, a powerful text-mode MySQL and InnoDB monitoring tool. It has lots of features, is fast and configurable, and it's easy to use.