Description:
When using SHOW CREATE TABLE, the index type is not shown if it is the default.
Apart from being inconsistent, this might create conflicts in future releases if the default index type of a table comes under user/DBA control.
How to repeat:
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)
# Use the non-default BTREE index type and note that it appears in SHOW CREATE TABLE
mysql> CREATE TABLE t (i int, index using btree (i)) engine=memory;
Query OK, 0 rows affected (0.06 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int(11) default NULL,
KEY `i` TYPE BTREE (`i`)
) ENGINE=HEAP DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> drop table if exists t;
Query OK, 0 rows affected (0.01 sec)
# Use default index type HASH -- it does not show up in SHOW CREATE TABLE
mysql> CREATE TABLE t (i int, index using hash (i)) engine=memory;
Query OK, 0 rows affected (0.06 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int(11) default NULL,
KEY `i` (`i`)
) ENGINE=HEAP DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Suggested fix:
Make SHOW CREATE TABLE always indicate the index type, irrespective of whether it's the default.
Description: When using SHOW CREATE TABLE, the index type is not shown if it is the default. Apart from being inconsistent, this might create conflicts in future releases if the default index type of a table comes under user/DBA control. How to repeat: mysql> drop table if exists t; Query OK, 0 rows affected (0.00 sec) # Use the non-default BTREE index type and note that it appears in SHOW CREATE TABLE mysql> CREATE TABLE t (i int, index using btree (i)) engine=memory; Query OK, 0 rows affected (0.06 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int(11) default NULL, KEY `i` TYPE BTREE (`i`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> drop table if exists t; Query OK, 0 rows affected (0.01 sec) # Use default index type HASH -- it does not show up in SHOW CREATE TABLE mysql> CREATE TABLE t (i int, index using hash (i)) engine=memory; Query OK, 0 rows affected (0.06 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `i` int(11) default NULL, KEY `i` (`i`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Suggested fix: Make SHOW CREATE TABLE always indicate the index type, irrespective of whether it's the default.