0

I saw a database schema that looked like this

CREATE TABLE foo (
    name_hash bigint,
    name varachar(500),
    a varchar(500),
    b varchar(500),
    ...
    PRIMARY KEY (name_hash),
    KEY ...
);

This seems to be an attempt to limit index size, by using a 8-byte integer rather than a 100-byte string. When looking up a value by name, the application hashes it, and then uses that hash in the SQL query instead of the name.

This is rather tedious, and I'm not sure if it is necessary.


Is there a feature of MySQL InnoDB that does something similar -- that looks up a string by its much shorter hash in order to fit index into memory?

Or does it do something like that already?

  • How are you going to handle hash conflicts if a hash is used as the primary key? – akuzminsky Jun 3 '15 at 23:12
  • @akuzminsky, in this case, they would fail. For 10 million records, there is a 0.0003% chance of two records having the same 8-byte hash. I suppose it could be a non-unique key instead. – Paul Draper Jun 3 '15 at 23:20
  • ok. the hash as the primary key will make non-leaf pages more dense and the tree shallower. But leaf pages (with the rest of fields) will still go to memory. – akuzminsky Jun 3 '15 at 23:24
1

InnoDB has no tool to do anything like what you describe.

InnoDB has a limit of 767 bytes per column in an index. That has room for a VARCHAR(255) utf8 or a VARCHAR(191) utf8mb4. Also, InnoDB wants to put long VARCHARs in a different block if the whole record is bigger than about 8KB. Will this be common? (Don't blindly use VARCHAR(500) when you can reasonably state a smaller limit.)

Plan A: Compress (in the client) the name and store it into VARBINARY(255). Assuming it is typical text, the compression will be about 3:1. Use that instead of the Hash.

Plan B: Split the name into 2 or 3 columns so the index limit is abided by. (An ugly solution!)

Plan C: Change the 767 limit. (It's possible, but I forget the details at the moment.)

Something to note: Any decent "hash" is very random. That is, each new row will land in some random place in the table. Once the table exceeds innodb_buffer_pool_size, you will be doing more and more I/O, thereby slowing down.

What will the SELECTs be like? Will you have other indexes on this table? Will you JOIN on name_hash? All of these may have a bearing on the 'best' way to design the schema.

  • Plan D: Upgrade to 5.7, which raises the limit. – Rick James Jan 12 '17 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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