www.nerdscene.com

I’ve been working on a project over the past few days and I came in to a situation where a table I had designed was purposefully created to NOT rely on auto_increment/numerical IDs so that I wouldn’t run in to any eventual limitations on the number of rows I could have (before the auto_increment field reached it’s max value and could no longer allow creation of new records). The table in question was made to use VARCHARs instead as my indexes so that I could link to other tables with those instead.

Then I noticed a slight issue when it came time to updating records in this table – if I was going to update a row, I had to compare almost all the fields in my table since I had no unique/primary keys defined (just 2 indexes on VARCHARs). I thought about how much simpler it would be if I had a unique ID field that would allow me to easily refer to a specific row of data – but at the same time I didn’t want to walk in to any data storage limits (though I know, over 4 billion or whatever the max INT is won’t likely ever be reached, it’s still a limitation I’d like to avoid by design).

I thought about it for a while and figured that it would be really nice if I could add an auto_increment field – but tie it in with another non-unique field (think of groups, and then in that group I count the records independently of other groups). It was an amazing idea but I had no clue how to make it work. All I knew is that I could define a UNIQUE KEY and constrain it to multiple fields like so “ALTER TABLE example ADD UNIQUE KEY id (field1, field2);”. The problem I foresaw was that if I made one of those fields auto_increment, MySQL would automatically create an INDEX for that same field to which it would store the increment count meaning that my auto_increment field would pertain to the entire table (meaning that the auto_increment field would increment irregardless of the other field specified in the UNIQUE KEY).

I then crawled the MySQL documentation wishing that there was some INSERT syntax that allowed subqueries, or a GROUP BY clause so I could use MAX() to count the highest ID number in my 2nd field and sort of fake an auto_increment field – but I had no such luck. So I figured “hey, why not just see what happens if I set auto_increment on the field” – so I did, to give me a table structure just like this:

CREATE TABLE `example` (
`domain` varchar(255) NOT NULL,
`id` int(10) unsigned NOT NULL auto_increment,
`data` varchar(255) default NULL,
UNIQUE KEY `id` (`domain`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I was surprised when I noticed that MySQL did not auto-create a separate INDEX for just the ID field! I just had to test this out now! I was on the verge of creating exactly what I had hoped to. So I quickly inserted some records:

INSERT INTO `example` (`domain`, `data`) VALUES ('domain.com', 'test1');
INSERT INTO `example` (`domain`, `data`) VALUES ('domain.com', 'test2');
INSERT INTO `example` (`domain`, `data`) VALUES ('domain.net', 'test1');
INSERT INTO `example` (`domain`, `data`) VALUES ('domain.net', 'test2');
INSERT INTO `example` (`domain`, `data`) VALUES ('domain.org', 'test5');

… and to my amazement, it actually worked! The “id” field auto_increments as a group in conjunction with the “domain” field. If you don’t understand what this all means – after inserting that data, this is what it looked like:

+------------+---+-------+
| domain.com | 1 | test1 |
| domain.com | 2 | test2 |
| domain.net | 1 | test1 |
| domain.net | 2 | test2 |
| domain.org | 1 | test5 |
+------------+---+-------+

The “cool factor” is in the IDs – look at how they’re auto_increment-ing but only in relation to a common domain name in the first field. So if I were to insert another record for “domain.com”, the next auto_increment ID would be 3 – but if I insert another record for “domain.org”, it would be 2 (since there’s only 1 record for that domain currently).

Neat eh?! This is a very cool “trick” I guess you could say – and it can be EXTREMELY useful!