[MDEV-13445] Hash-Index Type for InnoDB Created: 2017-08-04  Updated: 2023-10-18

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: None

Type: Task Priority: Major
Reporter: Stefan Böther Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: btree, data-vault, hash-join, innodb, md5

Issue Links:
Relates
relates to MDEV-371 Unique indexes for blobs Closed
relates to MDEV-31072 InnoDB is USING HASH and Optimizer is... Open
relates to MDEV-31615 SHOW INDEX returns Index_type BTREE f... Closed

 Description   

I want to use Hash Indexes also on the Storage Engine InnoDb.

I have a lot of Hash-Values in my table's which I create via hex(md5(<value>)) function and store them in a BINARY(16) column.

In my JOIN's I using them via USING(column), I not order them. So I would expect dramatic performance improvements if the underlying Index is not a B-Tree but also Hashing (ideal the value itself is used also in the index).

I doing this for a Data-Vault 2.0 Core Model which heavy use MD5 represantation of Business-Key for references the SAT's and the HUB's and the LNK's.

 Comments   
Comment by Marko Mäkelä [ 2023-04-18 ]

I wonder if MDEV-371 already implemented this.

Comment by Sergei Golubchik [ 2023-05-26 ]

Yes, one can say that MDEV-371 is basically a hash index, so this can be closed.
But optimizer doesn't use MDEV-371 indexes, iirc, so it needs a new task to implement this optimization.

Comment by Rick James [ 2023-10-18 ]

Maybe relates to MDEV-27653 long uniques don't work with unicode collations?

Generated at Thu Feb 08 08:05:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.