mysqldump: How to include index type?

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 1

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.