Maximize database performance for a very long digit

Question

I have a digitized image hash, the hash is like 2k integers. What is the best solution for database storage and search? The number of rows will be at least 3 million. Performance Suggestions? I am going to create a sorting column utf8_bin and convert all digits to a case-sensitive hash code and add an index to the column, or is there any other better solution?

Ps hash can be changed, 1k integers will be less accurate, so I prefer to store 2k or so.

Answer 1

The most compact way to store data is to store binary bytes using the VARBINARY data type, not a string with the utf8_bin setting.Calculate your digital hash of your image, convert to a string of hexadecimal digits, then use UNHEX () to convert to binary bytes. Binary bytes are stored in half the space of an equivalent string of hexadecimal digits. For example, a type string 'FFFF'requires four characters, but UNHEX('FFFF')is stored in two binary bytes.

Saving in a more compact way is just a small performance improvement.
The best performance advantage is to use an index. But InnoDB has a limit on the length of the index. The default limit is 767 bytes.

innodb_large_prefix=1, InnoDB 3072 ( DYNAMIC COMPRESSED, , ). , .

---

: , innodb_large_prefix MySQL 5.7.7 MariaDB 10.2, . , , . , .

CREATE TABLE MyTable (
  dhash VARBINARY(3072) NOT NULL,
  UNIQUE KEY (dhash)
);

Answer 2

• MD5 128 16 BINARY(16) 9 , 9 . 3 .

• , 2K. ( ?) md5. ( sha1 sha256 ..) .

• utf8 , ; CHARACTER SET ascii COLLATE ascii_bin. ( BINARY , , , .)

• VAR..., blob .

• 767, - 2 * 767 UNHEX() BINARY (767). ( `VARBINARY (767), .)

• 4 3072 5.7.7: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes