MySQL InnoDB table with a Hash Index

Question

I have a table like this.

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Later i have created a HASH index like this.

CREATE INDEX index ON table (column) USING HASH;

Latter i have try some explain queries.

Like

explain Select * from table where column=132;

And i see the engine is using the index on possible_keys and in the key stuff says the name of the index!!

But in the docs says that InnoDB doesn't allow hash index now i wonder why my innoDB Supposedly allows the hash index?

Answer 1

InnoDB silently changes "HASH" into "BTree". A BTree index does what a HASH does, plus more. Or do you think there is some good reason to want Hash?

"Good reason" -- MySQL was created many years ago. It was designed to be 'lean and mean'. Many features were boiled down to "one size fits all": BTree for indexing; Nested Loop Join for JOINing, etc.

Meanwhile, for future expansion and pseudo compatibility, some common syntax variants were included -- HASH for indexing, DESC for index ordering, etc. Even though those "lie" about what will happen, the database engine still gives you the 'right' answer.

Over time, the most glaring shortcuts have been remedied.

• Replication (3.xx?)
• Transactions (Adding InnoDB in 4.0) (MyISAM had LOCK TABLES, but that was not really adequate.)
• information_schema (4.1?) (versus a variety of SHOW commands) Note: 8.0 overhauled it with the "data dictionary")
• Character sets and collations (4.1) (vs "latin_swedish_ci", which was good enough for the implementor.)
• Stored routines (vs client code) (5.0)
• Subqueries (TEMPORARY TABLEs were not adequate)
• Various JOIN optimizations (5.6, 5,7, 8.0)
• only_full_group_by (MariaDB 10.1?, 5.7)
• ALTER not 'always' copying the table over (mostly 5.7)
• "Generated" columns (5.7)
• "Tablespaces" (5.7)
• JSON datatype and functions
• FULLTEXT and SPATIAL indexing in InnoDB (5.7, 8.0) (so MyISAM can be deprecated)
• DESC in INDEXes (8.0) (very few use cases really need this)
• "Windowing" functions (MariaDB 10.2, then MySQL 8.0)
• CTEs (MariaDB 10.2, then MySQL 8.0)
• Security: Better password handling (4.1?, 5.6, 8.0)
• HA (High Availability) (MariaDB with Galera; 8.0 with InnoDB Cluster)
• At-rest encryption (8.0?)

Notice how the list is somewhat ordered from "must have" to "nice to have". Yet to come may include

• Multi-threaded execution (Useless if you are I/O-bound anyway) (a very few use cases in 8.0)
• HASH indexing (and other types) (MariaDB 10.4, only for UNIQUE on TEXT/BLOB)
• Global UNIQUE and FOREIGN KEY for PARTITIONing. (Not that partitioning is very useful.)
• More syntax compatibility with standards and other vendors (MariaDB already does a much better job of this)

Meanwhile, some things are going away (or have already gone away -- either in MariaDB or MySQL)

• Compiling for a large variety of computers -- such as Atari
• The Query Cache -- Handy for benchmarking, but not really useful in Production environments. And a major hassle to implement in any 'cluster' topology.
• MyISAM has major deficiencies relative to InnoDB, and has very few benefits. (Arguably, the only benefit is less disk space needed.)

Answer 2

The feature in InnoDB is called Adaptive Hash Index,

Whether to use hash index depends on the scale of the table and query frequency, it's a completely internal strategy and normally out of configuration.

https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html