Hash index for unique string column being set to BTree-CodePudding

I am creating a table abc with id and a string. The string is unique and I want to assign HASH index to the string.

CREATE TABLE abc(
    id int unique auto_increment primary key ,
    unique_str varchar(40) not nulL,
    CONSTRAINT is_unique UNIQUE (unique_str)
);

create index string_index using hash on abc(unique_str);

show index from abc;

But the output shows that BTREE is being used for the index, although I have defined HASH.

Is there anything I am missing out?

PS. I am using Mysql 8.

CodePudding user response：

Presumably the unique constraint on unique_str in the original create table definition is implemented by a B-tree unique index. Try removing this constraint:

CREATE TABLE abc (
    id INT UNIQUE AUTO_INCREMENT PRIMARY KEY,
    unique_str VARCHAR(40) NOT NULL
);

CREATE INDEX string_index USING HASH ON abc(unique_str);

CodePudding user response：

HASH index type is not supported for MyISAM/InnoDB, it is supported for MEMORY/HEAP and NDB engines only.

See https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-storage-engine-index-types