[MDEV-31615] SHOW INDEX returns Index_type BTREE for Index_type HASH Created: 2023-07-04  Updated: 2023-07-04  Resolved: 2023-07-04

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.6.4, 10.6.10, 11.0.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sebastian Krüger Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: btree, hash, index
Environment:

tested on Ubuntu 22.04 and Ubuntu 20.04

Attachments: PNG File show_index.png     PNG File structure.png    
Issue Links:
Relates
relates to MDEV-13445 Hash-Index Type for InnoDB Open

 Description   

Lets have the following table:

CREATE TABLE `example` (
 
  `example_id` int(11) NOT NULL,
 
  `index_btree` int(11) NOT NULL,
 
  `index_hash` int(11) NOT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
 
 
 
ALTER TABLE `example`
 
  ADD PRIMARY KEY (`example_id`),
 
  ADD KEY `example_hash` (`index_hash`) USING HASH,
 
  ADD KEY `example_btree` (`index_btree`) USING BTREE;
 
 
 
ALTER TABLE `example`
 
  MODIFY `example_id` int(11) NOT NULL AUTO_INCREMENT;

Now we have a look at the index by

SHOW INDEX FROM example;

And see that the Index_type is BTREE for Index example_hash.

As a result of this PHPMyadmin also shows the wrong type. In a created export the index_type is correctly set to HASH...

 Comments   
Comment by Alice Sherepa [ 2023-07-04 ]

HASH index type is only supported for Memory engine (https://mariadb.com/kb/en/storage-engine-index-types/). InnoDB silently changes "HASH" into "BTree" (compatibility reasons, etc. )

Generated at Thu Feb 08 10:25:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.