How to determine if an index is "USING HASH"?

Question

I've got this table definition in MySQL 5.6.39:

CREATE TABLE `_fulltexttest` (
  `hello` text NOT NULL,
  `bar` varbinary(255) NOT NULL,
  `baz` varbinary(255) NOT NULL,
  UNIQUE KEY `whatev2` (`baz`) USING HASH,
  KEY `whatev` (`bar`) USING HASH,
  KEY `baz` (`baz`) USING HASH,
  KEY `baz_2` (`baz`) COMMENT 'bacon',
  FULLTEXT KEY `hello` (`hello`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

As you can see, a few of the indexes have USING HASH.

However, when I try:

 show indexes from _fulltexttest in mydb;

Or:

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' and TABLE_NAME='_fulltexttest'

Neither shows "HASH" anywhere in there. The INDEX_TYPE is BTREE.

But if I do a SHOW TABLE, it shows up in the definition, so clearly it must be saved somewhere.

What query can I run to get the full index definition, including the USING HASH part?

Answer 1

You've done the right thing for determining whether the index is using hash.

The answer is: it isn't.

The documentation for MySQL 5.6 says they haven't implemented it for InnoDB at all yet.