MySQL schema for table of hashes
I need to store, query and update a large amount of file hashes. What would be the optimal mysql schema for this kind of table? Should I use a hash index eg.
CREATE INDEX hash_index on Hashes(id) using HASH;
can I reuse the PK hash for the index ? (as I understand, the "using hash" will create a hash from the hash)
mysql
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
add a comment |
I need to store, query and update a large amount of file hashes. What would be the optimal mysql schema for this kind of table? Should I use a hash index eg.
CREATE INDEX hash_index on Hashes(id) using HASH;
can I reuse the PK hash for the index ? (as I understand, the "using hash" will create a hash from the hash)
mysql
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
1
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes". source dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html with other words you can't create a HASH index on a "normal" table
– Raymond Nijland
Nov 12 '18 at 11:53
the "create index" command works but it seems to create a btree index. I was wondering what would be the optimal schema for such a table.
– Tohmaxxx
Nov 12 '18 at 11:57
The scary bit is how you represent variable length paths, not the hash.
– symcbean
Nov 12 '18 at 12:44
add a comment |
I need to store, query and update a large amount of file hashes. What would be the optimal mysql schema for this kind of table? Should I use a hash index eg.
CREATE INDEX hash_index on Hashes(id) using HASH;
can I reuse the PK hash for the index ? (as I understand, the "using hash" will create a hash from the hash)
mysql
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
I need to store, query and update a large amount of file hashes. What would be the optimal mysql schema for this kind of table? Should I use a hash index eg.
CREATE INDEX hash_index on Hashes(id) using HASH;
can I reuse the PK hash for the index ? (as I understand, the "using hash" will create a hash from the hash)
mysql
mysql
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
asked Nov 12 '18 at 11:49
TohmaxxxTohmaxxx
1766
1766
1
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes". source dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html with other words you can't create a HASH index on a "normal" table
– Raymond Nijland
Nov 12 '18 at 11:53
the "create index" command works but it seems to create a btree index. I was wondering what would be the optimal schema for such a table.
– Tohmaxxx
Nov 12 '18 at 11:57
The scary bit is how you represent variable length paths, not the hash.
– symcbean
Nov 12 '18 at 12:44
add a comment |
1
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes". source dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html with other words you can't create a HASH index on a "normal" table
– Raymond Nijland
Nov 12 '18 at 11:53
the "create index" command works but it seems to create a btree index. I was wondering what would be the optimal schema for such a table.
– Tohmaxxx
Nov 12 '18 at 11:57
The scary bit is how you represent variable length paths, not the hash.
– symcbean
Nov 12 '18 at 12:44
1
1
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes". source dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html with other words you can't create a HASH index on a "normal" table
– Raymond Nijland
Nov 12 '18 at 11:53
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes". source dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html with other words you can't create a HASH index on a "normal" table
– Raymond Nijland
Nov 12 '18 at 11:53
the "create index" command works but it seems to create a btree index. I was wondering what would be the optimal schema for such a table.
– Tohmaxxx
Nov 12 '18 at 11:57
the "create index" command works but it seems to create a btree index. I was wondering what would be the optimal schema for such a table.
– Tohmaxxx
Nov 12 '18 at 11:57
The scary bit is how you represent variable length paths, not the hash.
– symcbean
Nov 12 '18 at 12:44
The scary bit is how you represent variable length paths, not the hash.
– symcbean
Nov 12 '18 at 12:44
add a comment |
1 Answer
1
active
oldest
votes
File hashes are fixed-length data items (unless you change the hash type after you have created some rows). If you represent your file hashes in hexadecimal or Base 64, they'll have characters and digits in them. For example, sha-256 hashes in hex take 64 characters (four bits in each character).
These characters are all 8-bit characters, so you don't need unicode. If you're careful about filling them in, you don't need case sensitivity either. Eliminating all these features of database columns makes the values slightly faster to search.
So, make your hashes fixed-length ASCII columns, using ddl like this:
hash CHAR(64) COLLATE 'ascii_bin'
You can certainly use such a column as a primary key.
Raymond correctly pointed out that MySQL doesn't offer hash indexes except for certain types of tables. That's OK: ordinary BTREE indexes work reasonably well for this kind of information.
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53261548%2fmysql-schema-for-table-of-hashes%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
File hashes are fixed-length data items (unless you change the hash type after you have created some rows). If you represent your file hashes in hexadecimal or Base 64, they'll have characters and digits in them. For example, sha-256 hashes in hex take 64 characters (four bits in each character).
These characters are all 8-bit characters, so you don't need unicode. If you're careful about filling them in, you don't need case sensitivity either. Eliminating all these features of database columns makes the values slightly faster to search.
So, make your hashes fixed-length ASCII columns, using ddl like this:
hash CHAR(64) COLLATE 'ascii_bin'
You can certainly use such a column as a primary key.
Raymond correctly pointed out that MySQL doesn't offer hash indexes except for certain types of tables. That's OK: ordinary BTREE indexes work reasonably well for this kind of information.
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
add a comment |
File hashes are fixed-length data items (unless you change the hash type after you have created some rows). If you represent your file hashes in hexadecimal or Base 64, they'll have characters and digits in them. For example, sha-256 hashes in hex take 64 characters (four bits in each character).
These characters are all 8-bit characters, so you don't need unicode. If you're careful about filling them in, you don't need case sensitivity either. Eliminating all these features of database columns makes the values slightly faster to search.
So, make your hashes fixed-length ASCII columns, using ddl like this:
hash CHAR(64) COLLATE 'ascii_bin'
You can certainly use such a column as a primary key.
Raymond correctly pointed out that MySQL doesn't offer hash indexes except for certain types of tables. That's OK: ordinary BTREE indexes work reasonably well for this kind of information.
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
add a comment |
File hashes are fixed-length data items (unless you change the hash type after you have created some rows). If you represent your file hashes in hexadecimal or Base 64, they'll have characters and digits in them. For example, sha-256 hashes in hex take 64 characters (four bits in each character).
These characters are all 8-bit characters, so you don't need unicode. If you're careful about filling them in, you don't need case sensitivity either. Eliminating all these features of database columns makes the values slightly faster to search.
So, make your hashes fixed-length ASCII columns, using ddl like this:
hash CHAR(64) COLLATE 'ascii_bin'
You can certainly use such a column as a primary key.
Raymond correctly pointed out that MySQL doesn't offer hash indexes except for certain types of tables. That's OK: ordinary BTREE indexes work reasonably well for this kind of information.
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
File hashes are fixed-length data items (unless you change the hash type after you have created some rows). If you represent your file hashes in hexadecimal or Base 64, they'll have characters and digits in them. For example, sha-256 hashes in hex take 64 characters (four bits in each character).
These characters are all 8-bit characters, so you don't need unicode. If you're careful about filling them in, you don't need case sensitivity either. Eliminating all these features of database columns makes the values slightly faster to search.
So, make your hashes fixed-length ASCII columns, using ddl like this:
hash CHAR(64) COLLATE 'ascii_bin'
You can certainly use such a column as a primary key.
Raymond correctly pointed out that MySQL doesn't offer hash indexes except for certain types of tables. That's OK: ordinary BTREE indexes work reasonably well for this kind of information.
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
answered Nov 12 '18 at 12:19
O. JonesO. Jones
59.7k972106
59.7k972106
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53261548%2fmysql-schema-for-table-of-hashes%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
"Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes". source dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html with other words you can't create a HASH index on a "normal" table
– Raymond Nijland
Nov 12 '18 at 11:53
the "create index" command works but it seems to create a btree index. I was wondering what would be the optimal schema for such a table.
– Tohmaxxx
Nov 12 '18 at 11:57
The scary bit is how you represent variable length paths, not the hash.
– symcbean
Nov 12 '18 at 12:44