Xaprb

There’s a really important difference between a unique index (MySQL’s answer to a “unique constraint”) and a primary key in MySQL. Please take a look at this:

CREATE TABLE `t` (
  `a` int,
  `b` int,
  `c` int,
  UNIQUE KEY `a` (`a`,`b`)
)

The combination of columns a, b should uniquely identify any tuple in the table, right?

select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
+------+------+------+

Wrong. Our arch-enemy NULL messes things up again:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL

MySQL doesn’t let you define a primary key over nullable columns, for this reason. This is as of version 4.0, I believe — I recall that in version 3.23 there was nothing special about a primary key; it was just a unique non-nullable index named PRIMARY.

A lot of times I’m looking at several time-series samples of numbers embedded in free-form text, and I want to know how the numbers change over time. For example, two samples of SHOW INNODB STATUS piped through grep wait might contain the following:

Mutex spin waits 0, rounds 143359179688, OS waits 634106844
RW-shared spins 1224152309, OS waits 38278807; RW-excl spins 2432166425, OS waits 35264871
Mutex spin waits 0, rounds 143386303439, OS waits 634292093
RW-shared spins 1224197048, OS waits 38281423; RW-excl spins 2432347936, OS waits 35271423

How much have the numbers changed in the second sample? My head is too lazy to do that math. So Daniel Nichter and I whipped up Yet Another Snippet to self-discover patterns of text and numbers, and compare each line against the previous line that matches the same pattern. Let’s fetch it:

wget http://maatkit.googlecode.com/svn/trunk/util/rel

Now give it the above input, and it’ll print out something useful (emphasis mine):

Mutex spin waits 0, rounds 143359179688, OS waits 634106844
RW-shared spins 1224152309, OS waits 38278807; RW-excl spins 2432166425, OS waits 35264871
Mutex spin waits 0, rounds 27123751, OS waits 185249
RW-shared spins 44739, OS waits 2616; RW-excl spins 181511, OS waits 6552

My lazy brain likes that much better.