1

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?

2
  • 2 seem to be good one as with about 200M records it may not be a good idea to index uri_hash(255), have you tested with 200M records?..do run optimize/analyze table before you do benchmarking Nov 22, 2016 at 13:20
  • @NawazSohail, I tested it with 70M records and have not found a difference in performance. Average links length is 51 chars.
    – Alexey
    Nov 29, 2016 at 21:59

1 Answer 1

3

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.

6
  • 16GB of RAM will probably suffice for now; 32 or 48 will be needed for when you have 200M rows.
    – Rick James
    Dec 6, 2016 at 2:07
  • UNIQUE KEY i_2 (uri(255)) - it's old solution. Now I think about UNIQUE KEY i_2 (uri(1023)) vs UNIQUE KEY i_2 (md5_uri(16)) + uri column without index
    – Alexey
    Dec 6, 2016 at 2:50
  • "It's old solution" -- Shrinking the data and cutting out indexes are parts of the solution I am advocating.
    – Rick James
    Dec 6, 2016 at 18:24
  • I can't remove PRIMARY KEY (id) because it is needed for future migrations (using LHM, pt-online-schema-change or gh-ost)
    – Alexey
    Dec 6, 2016 at 19:12
  • I'm trying to understand what is better: id + varchar(1023) + binary(16) + unique key by binary(16) or id + varchar utf8_bin (1023) + unique key by varchar(1023), but I have not found the answer yet
    – Alexey
    Dec 6, 2016 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.