1

I have a table that contains data (blobs). Data is often read, rarely inserted, and never modified.

For all of this data, I compute hashes that I also store in the DB. These hashes are only checked when inserting new data, therefore, they are hardly used in normal, day-to-day routine.

My question is, is it worth the trouble to make a separate table for the hashes, is it reasonable to expect any performance gain for putting them aside? Or can I confidently just add an extra column to the table for the hashes, even though the hashes are rarely used?

(Although I would be interested in a general answer, I must point out that I use MariaDB and sqlite).

  • 1
    Youк "separate table for the hashes" will act by the same way as an index by this hash, but it needs additional support.Moreover, if DBMS supports the index by virtual calculated field this is the best solution. MySQL 8.0 Reference Manual / ... / Secondary Indexes and Generated Columns. – Akina Jan 17 at 15:56
  • Where exactly do you expect the performance gain to come from, considering that the entire table page is the minimum I/O unit? – mustaccio Jan 17 at 16:21
  • @mustaccio Perhaps a table with less columns is faster to query? Esp. since a hash can be long, e.g. a SHA1 key is 40 ascii characters, SHA2 hashes are even longer. – user209974 Jan 17 at 16:41
  • "Perhaps"? Do you have any numbers that show that? – mustaccio Jan 17 at 16:48
  • @mustaccio If I did, perhaps I wouldn't be asking this question? – user209974 Jan 17 at 16:49
1

(re: MySQL/MariaDB) It depends.

  • For small columns, say under 1KB, don't bother with the hash.
  • For large columns, say over 7KB, it may not matter -- InnoDB puts large columns "off-record" in a block separate from the main part of the record.
  • For medium-sized columns, say 1KB-7KB, the column is likely to be kept "on record", thereby making any action on the row fetch the bulky column, too. This is even if you don't say SELECT *.
  • Regardless of what you do, avoid using SELECT *; instead, spell out the columns you need. (* will bite you if you ever add a column to the table.)
  • Pack the hash into BINARY(16) for MD5, etc., rather than a VARCHAR of twice the size.
  • The time taken to fetch a row is more than unpacking the columns. That is, "how many columns" is not a metric to worry about. (No, I don't have numbers to back that up; just decades of experience.)

The hash (whether md5, sha1, etc) is a bigger nuisance. It is very 'random', thereby leading to cache misses when inserting a new hash or looking up by the hash. This issue comes into play when the BTree with the hash is bigger than can be cached in the buffer_pool. To phrase it another way, use of a hash on a billion-row table will slow down all accesses to the speed of the disk. And it will slow down other processes due to cache misses.

InnoDB blocks are 16KB; the underlying OS or drive block size does not factor into this discussion.

In the opposite direction, consider putting frequently updated columns (clicks, Likes, views) in a separate table. (But that's another Q&A.)

|improve this answer|||||

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.