Multi column sequences in MySQL

One of the most common used attributes in MySQL is definitely AUTO_INCREMENT. This is quite helpful when one needs to generate unique identities for the table rows. By default when a AUTO_INCREMENT column is the only column in a index, whether PRIMARY KEY or UNIQUE, it generates a single monotonic sequence of numbers : 1,2,3,4,… etc. But for MyISAM storage engine it is possible to create complex sequences in a table containing an AUTO_INCREMENT column.

Take the simple table definition below:

CREATE TABLE directors
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    director VARCHAR(100) NOT NULL,
    film VARCHAR(100)
 
) ENGINE = MyISAM;

Add a few rows to the table and you can see the ‘id’ column sequence marching steadily in a uniform fashion.

+----+------------+-----------------------+
| id | director   | film                  |
+----+------------+-----------------------+
|  1 | spielberg  | The Sugarland Express |
|  2 | spielberg  | Minority Report       |
|  3 | spielberg  | Empire of the Sun     |
|  4 | soderbergh | Traffic               |
|  5 | soderbergh | Erin Brockovich       |
|  6 | spielberg  | The Goonies           |
+----+------------+-----------------------+

Complex sequences

Linear sequences are fine, but sometimes we need to generate some complex sequence in our AUTO_INCREMENT column. A complex sequence is something that does not increase monotonically, but according to some pattern. In our example we want a separate linear sequence in the ‘id’ column for each director. To create a such a sequence we change the table definition a little by combining two columns for the PRIMARY KEY; the ‘id’ column (which is a AUTO_INCREMENT type) and the ‘director’ column.

CREATE TABLE directors
(
    id INT NOT NULL AUTO_INCREMENT,
    director VARCHAR(100) NOT NULL,
    film VARCHAR(100),
    PRIMARY KEY (director, id)
 
) ENGINE = MyISAM;

Now add a few rows and the below table is what you will get.

+----+------------+-----------------------+
| id | director   | film                  |
+----+------------+-----------------------+
|  1 | spielberg  | The Sugarland Express |
|  2 | spielberg  | Minority Report       |
|  3 | spielberg  | Empire of the Sun     |
|  1 | soderbergh | Traffic               |
|  2 | soderbergh | Erin Brockovich       |
|  4 | spielberg  | The Goonies           |
+----+------------+-----------------------+

Although the ‘id’ column may now look random, sorting it by the ‘director’ field will make it clear. The ‘id’ column sequence is now linked up with the ‘director’ field.

mysql> SELECT * FROM directors ORDER BY director;
 
+----+------------+-----------------------+
| id | director   | film                  |
+----+------------+-----------------------+
|  1 | soderbergh | Traffic               |
|  2 | soderbergh | Erin Brockovich       |
|  1 | spielberg  | The Sugarland Express |
|  2 | spielberg  | Minority Report       |
|  3 | spielberg  | Empire of the Sun     |
|  4 | spielberg  | The Goonies           |
+----+------------+-----------------------+

In the above example we have a two column index, but the concept can be applied to a n-column index, where the last column is the AUTO_INCREMENT column. MySQL than generates a independent sequence for each unique non-AUTO_INCREMENT column combination.

Sequence calculation

Incidentally, this is how MySQL calculates the next sequence number for a multi column index.

MAX(auto_increment_column) + 1 WHERE prefix=given-prefix

So to get the next sequence number for ‘soderbergh’, MySQL runs something like the following query.

SELECT MAX(id) + 1 FROM directors WHERE director = 'soderbergh'

Some notes

Note:
The ‘id’ (AUTO_INCREMENT) field comes last in the key definition. Not placing the AUTO_INCREMENT field last will give you a normal AUTO_INCREMENT sequence.

If any of the non AUTO_INCREMENT columns to be indexed will contain a NULL value, create a UNIQUE index instead of a PRIMARY KEY.