Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?

Question

I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc.

YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * 64). This would be more than enough for my needs.

My only pondering is whether I should be storing them as ints in the database and converting them every time a request comes in (or when I need to generate a url), or storing them as the string that the user will see. And why?

A couple of follow-up questions:

1) rather than generating random integers and converting them to the base64 string, should I use auto-increment numbers with a salt to randomise the alphabet, so I can get apparently "random" identifiers from IDs of 1 2 3 4 5 6 7 etc. And why?

2) for table relationships should I use whatever I've generated as my PK/FK, or should that be a separate column and just a simple auto-increment int, for some reason?

Answer 1

In SQL Server, I would store this as an int, and use CRYPT_GEN_RANDOM(4) to generate cryptographically random numbers for each row.

Here's an example:

DECLARE @c int;
DECLARE @c_base64 varchar(8);
DECLARE @c_converted int;

--get the cryptographically random integer
SELECT @c = CONVERT(int, crypt_gen_random(4));
--convert it into a base-64 string
DECLARE @c int;
DECLARE @c_base64 varchar(8);
DECLARE @c_converted int;

--get the cryptographically random integer
SELECT @c = CONVERT(int, crypt_gen_random(4));
--convert it into a base-64 string
SELECT @c_base64 = (SELECT CONVERT(varbinary(4), @c) FOR XML PATH(''), BINARY BASE64);
--convert it back to an int
SELECT @c_converted = CONVERT(int, (CONVERT(xml, @c_base64).value('.', 'varbinary(4)')));
--display the original, the string, and the converted string
SELECT original = @c, converted = @c_base64, unconverted = @c_converted;

The results look like:

+-----------+-----------+-------------+
| original  | converted | unconverted |
+-----------+-----------+-------------+
| 629493479 | JYVO5w==  |   629493479 |
+-----------+-----------+-------------+

Why? Storing integers will consume half the space of storing the converted string. This may not seem like much on disk, but if you have a table with half a billion rows, every byte counts. Wasting 2 bytes per row would mean you'd need an extra gigabyte of RAM to scan the table in that scenario.

Answer 2

The solution you are chasing

16M can be stored in a 3-byte MEDIUMINT UNSIGNED.

A 4-character base64 can be stored in a 4-byte CHAR(4) CHARACTER SET ascii COLLATE ascii_bin.

The 1-byte difference is probably less than 1% of the disk space consumed -- not worth worrying about.

Dynamically converting between the mediumint and char possibly consumes less than 1% of the CPU time.

The performance of mediuming vs char(4) -- also a tiny difference.

I have a rule of thumb: If a proposed change does not improve some metric (typically speed or space) by at least 10%, I drop it. Instead, I go with what is easier to program.

Alternate solution

But... I question the use of base64. If the trailing = shows, then a user will guess that it is base64, convert to an int, and your cover is blown, at least if you are using sequential numbers.

If you are using 'random' numbers, then consider going with a bigger range (4-byte INT / CHAR(5) / whatever) and use MD5(CONCAT('secret salt', sequence)) and then peel off 30 bits of that. That can't be 'reversed', but you probably don't need to get the original int from the key.