Unique index by VARCHAR(1023) vs by BINARY(32)

Question

I have a table of unique urls (~70M records, an average urls length is 51 chars):

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uri` varchar(1023) NOT NULL,
  `uri_hash` varchar(64) DEFAULT NULL,  
  PRIMARY KEY (`id`),
  UNIQUE KEY `i_1` (`short_url_code`),  
  UNIQUE KEY `i_2` (`uri`(255))
) ENGINE=InnoDB CHARSET=utf8;

uri_hash = hash(uri)

It was created when MySQL did not support long unique indexes. Now we have MySQL 5.7 and I think about optimisation, but I'm not sure wich way is better:

1. remove uri_hash column and make index i_2 longer (1023), and update uri column to support different cases (upper/lower) CHARACTER SET utf8 COLLATE utf8_bin

2. remove index i_2 that we don't use and search by uri_hash

   a. leave uri_hash varchar(64)

   b. convert to uri_hash binary(32)

3. leave as is

I have done some benchmarks, but I have not found significant difference between these variants: the same 60 reads + 60 writes actions per second in one thread

This table will grow up to ~200M records.

Can you suggest what variant is better?

Answer 1

First, I must point out why this is bad:

UNIQUE KEY `i_2` (`uri`(255))

It constrains only the first 255 characters of uri to be unique. That is, you cannot have two rows that agree in the first 255 characters, but differ after that.

Where is short_url_code?

Back to the question... Answer: "none of the above". But here are some tips on improving performance:

If you need a uniqueness constraint on a too-long column, here is one approach:

Use UNHEX(MD5(uri)) in a BINARY(16). Then make that the PRIMARY KEY and get rid of id.

For those who complain that MD5 might have dups, I point out that "If you have 9 trillion rows, there is only one chance in 9 trillion that the next row you insert will be a false dup."

So, I would have only these two indexes:

PRIMARY KEY(md5_uri),
INDEX(uri(44))

The secondary index assumes you need to look up uri prefixes when you don't know the full string. The 44 was somewhat arbitrarily picked -- smaller to save space; larger to have fewer dups. More important than the average of 51 you mentioned is "what is the shortest length that leads to a 'reasonably small' number of dups for each prefix."

The problem with any type of hash is the inability to cache the data (and/or index) after the data (or index) becomes bigger than can fit in cache (innodb_buffer_pool_size).

My suggested changes will

• shrink amount of cache needed by getting rid of an index and a column, and
• have only 1 uniqueness constraint (instead of 3) to check during INSERT.

But you still need more ram than the dataset size, so that innodb_buffer_pool_size has space for caching all the data. Else, you will stuck with being as slow as your disk drive (which should like a slow spinning drive at 60/sec.) Are there more columns in the table than what you listed? If so, we might consider vertical partitioning.

That leaves another problem that you alluded to -- case folding. No hash is impervious to case without help. You might do UNHEX(MD5(LOWER(uri))). But that will also strip accents in utf8. Not knowing your full set of requirements here, I cannot discuss further.

Another thing to do is to avoid the at-least-one I/O that occurs with every transaction. Where practical, batch inserts, group things between BEGIN..COMMIT, and/or set innodb_flush_log_at_trx_commit=2 (instead of the default of 1). SSDs would also help.