What's the difference between using and not using BTREE in MySQL 'CREATE TABLE' statement?

Question

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 1

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.