Given the following:
Target platform: MySQL 5.7, using InnoDB.
Scenario: Storing hundreds of millions of email addresses (plus some properties not used in queries). All queries will be done by knowing the email address beforehand.
- SHA256 the email address.
- Shard "the email table" by taking the first 3 bytes of the ASCII SHA256, creating 4096 tables (from 0x000 to 0xFFF) that will act as buckets for the email addresses. This tries to avoid having one single huge table.
Question: Which of the following would be a good PK to use inside each one of those 4096 buckets in terms of performance (indexing as being more important than querying)? Use of disk space might not be that important upfront (unless there are some heavy arguments to take this into account, which I'm open to know and discuss, of course).
- VARCHAR(255) of the email address? This is of course the simplest.
- CHAR(64) of the ASCII SHA256 hash of the email address? Long shot: I'm considering that indexing and comparing fixed length strings (CHAR) is faster than variable length strings (VARCHAR).
- Split the SHA256 into 8 64bit integers, then create a composite PK of 8 BIGINT columns and index/query by those 8 BIGINT columns instead of using a VARCHAR/CHAR? Crazy idea: Perhaps using only 64bit integers for indexing and querying can provide a noticeable improvement in index and query performance (and also perhaps in disk access/storage). Although this is a composite PK of 8 BIGINT columns :\
Thanks in advance,
create table email_adresses (email varchar(255) not null, email_hash int as ( cast(conv(substring(md5(email), 1, 16), 16, 10) as unsigned integer) ) ) engine = innodb partition by hash (email_hash) partitions 12.