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.

• Jared Beck: This doesn't quite answer my question, but it is very useful. I did not know that BTREE was the only supported index type in InnoDB, for example. Thank you also for citing your sources.
• RolandoMySQLDBA: mysqldump is not designed to display USING BTREE. I remember years ago that older versions mysqldump used to but they may have been source compiled. The standard versions don't do this.
• RolandoMySQLDBA: I stand corrected by my own research. mysqldump can still dump USING BTREE.