What’s the difference between using and not using BTREE in MySQL ‘CREATE TABLE’ statement? Code Answer

I’m new to SQL, I want to create a table, I see an example:

DROP TABLE IF EXISTS `aaa`;
CREATE TABLE `aaa` (
`date` datetime DEFAULT NULL,
`id` varchar(200) DEFAULT NULL,
...
`source` varchar(200) DEFAULT NULL,
KEY `idx1` (`source`),
KEY `idx2` (`id`,`date`) USING BTREE,
KEY `idx3` (`date`) USING BTREE
) ENGINE=MyISAM

I’ve been reading online about USING BTREE but still a bit confused, why idx1 DOESN’T have it and idx2, idx3 have USING BTREE? What’s the difference and purpose? What’s the advantage of using it? Thanks.

Answer

No difference, other than syntax.

• The default is BTree.
• If you say nothing, or even if you say HASH, it will be silently converted to BTree.

Essentially BTree a good, all-purpose, indexing mechanism.

• Finding a specific row (“point query”) is very fast. Eg: WHERE x = 123
• Scanning a “range” or rows is very efficient. Eg: WHERE x BETWEEN 333 AND 444 (Contrast: Very slow for HASH.)

FULLTEXT and SPATIAL, if desired, require explicit specification.

You should really move from MyISAM to InnoDB.

See Wikipedia: MyISAM uses B-Tree; InnoDB uses B+Tree.