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.