snippetsqlMinor
mysqldump: How to include index type?
Viewed 0 times
includemysqldumptypehowindex
Problem
When I dump the schema of my database
I get lines like this
but I want lines like this
The indexes in question do, in fact, have
I have tried a variety of
I am using
mysqldump --no-data my_db > schema.sqlI get lines like this
KEY `index_tbl_on_col` (`col`)but I want lines like this
KEY `index_tbl_on_col` (`col`) USING BTREEThe 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)Solution
You should not worry about embedding the
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
You can look up the
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
Interestingly, there is sort of a bug report on this. You know who submitted it ? A guy named Jared.
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 BTREEInterestingly, there is sort of a bug report on this. You know who submitted it ? A guy named Jared.
Context
StackExchange Database Administrators Q#116058, answer score: 3
Revisions (0)
No revisions yet.