1

I'm noticing short URL sites are using alphnumeric id instead of integers for unqiue id.

Have anybody have experience with implementation? What is the best way to implement this feature in MySQL? I believe the tricky part would probably be the incrementation and indexing of the id.

example: bit.ly/AbC123

Yada
  • 30,349
  • 24
  • 103
  • 144

4 Answers4

5

I suspect most of these alphanumeric ids are just a numeric ID converted to Base 36 - see this sample stored function, or you could do it using a UDF.

ggiroux
  • 6,544
  • 1
  • 22
  • 23
3

Honestly, I'd just use an integer internally and then convert to an alphanumeric to expose to the user, if you must use an alphanumeric sequence. As an example encoding scheme, you could use each digit as an index into an array consisting of letters from A through J (corresponding to 0 through 9).

Integers are a much better unique id in general, because they take less memory and because they are more easily indexed.

David Pfeffer
  • 38,869
  • 30
  • 127
  • 202
1

I would generate the hash separately but maintain an integer (or BIGINT) column for the primary key. MySQL works best with smaller primary keys, especially InnoDB.

For example:

CREATE TABLE `urls` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hash` char(6) NOT NULL,
  `url` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Edit: For generating the hash see this SO post for a base62 converter implementation.

Community
  • 1
  • 1
hobodave
  • 28,925
  • 4
  • 72
  • 77
1

If you don't care about the length of an ID, consider using UUID().

There are also various means for server-side programming languages such as uniqid in PHP or Data::UUID in Perl.

codeholic
  • 5,680
  • 3
  • 23
  • 43