Question :
When I dump the schema of my database
mysqldump --no-data my_db > schema.sql
I get lines like this
KEY `index_tbl_on_col` (`col`)
but I want lines like this
KEY `index_tbl_on_col` (`col`) USING BTREE
The indexes in question do, in fact, have Index_type = 'BTREE'
.
show index from tbl;
| Table | Key_name | Index_type |
+-------+---------------+------------+
| tbl | ix_tbl_on_col | BTREE |
I have tried a variety of mysqldump
options including using --skip-opt --create-options
together.
I am using mysqldump
5.6.26 from homebrew.
mysqldump --version
mysqldump Ver 10.13 Distrib 5.6.26, for osx10.10 (x86_64)
Answer :
You should not worry about embedding the USING BTREE
onto the indexes. Why ?
The default index type for MyISAM and InnoDB is BTREE. You cannot impose any other type.
The default index type for MEMORY Storage Engine is HASH. You can impose a BTREE index type.
I wrote a post 4 years ago on HASH not being allowed for MyISAM and InnoDB: Why does MySQL not have hash indices on MyISAM or InnoDB?
In light of this, there is no need to explicitly tack on USING BTREE
.
You can look up the CREATE INDEX
Documentation to verify default index types per Storage Engine.
UPDATE 2015-09-24 16:23 EDT
Hey Jared, I got a surprise for you. I went Googling around and there is a way to spit out USING BTREE
. The option is –compatible=mysql40. I saw it in StackOverflow : MySQL error USING BTREE
Interestingly, there is sort of a bug report on this. You know who submitted it ? A guy named Jared.