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.
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:
UNHEX(MD5(uri)) in a
BINARY(16). Then make that the
PRIMARY KEY and get rid of
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:
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 (
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
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.