Why did a "USING BTREE" appear in "CREATE TABLE" with mySQL?

Question

I was examining my database tables, and something appeared I cant explain that:

CREATE TABLE  `challenges` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KIND` enum('1','2','3') NOT NULL DEFAULT '1',
  `TITLE` varchar(255) NOT NULL DEFAULT '',
  `DESCRIPTION` text NOT NULL,
  `DATEAT` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `OWNER_ID` int(10) unsigned NOT NULL DEFAULT '0',
  `SOLVEDREPLAY_ID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Index_2` (`OWNER_ID`),
  KEY `Index_4` (`DATEAT`),
  KEY `Index_3` (`SOLVEDREPLAY_ID`) **USING BTREE**,
  KEY `Index_5` (`KIND`) **USING BTREE**,
  CONSTRAINT `FK_challenges_1` FOREIGN KEY (`OWNER_ID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_challenges_2` FOREIGN KEY (`SOLVEDREPLAY_ID`) REFERENCES `uploads` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Index_3 and Index_5 is using btree (between the ** **, I couldnt make it bold), but interestingly the others arent. All index is "BTREE", I just dont understand why is it emphasize in the CREATE TABLE expression

Answer 1

It means that your indices are using a B-tree model under-the-hood, as opposed to a hashmap.

A B-tree is a generalisation of a binary tree. They are good for inequalities (<, >), ranges and ordering. Hashmaps do not support those things, but are faster for equal/not-equal checks. I'm not sure which performs better for a small enum set like you have for KIND.

The default is BTREE anyway, so those statements are just being explicit.

Here is a good reference for MySQL: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

Answer 2

A btree is data structure.

It is a good way of indexing data.

Perhaps google Btree