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

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of What’s the difference between using and not using BTREE in MySQL ‘CREATE TABLE’ statement? without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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.

We are here to answer your question about What’s the difference between using and not using BTREE in MySQL ‘CREATE TABLE’ statement? - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji