3

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)
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Jared Beck
  • 288
  • 3
  • 11

1 Answers1

3

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.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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. – Jared Beck Sep 24 '15 at 17:22
  • 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 Sep 24 '15 at 17:25
  • I stand corrected by my own research. mysqldump can still dump USING BTREE. – RolandoMySQLDBA Sep 24 '15 at 20:26