MySQL Bugs: #21031: Alter table to memory table defaults to hash indexes

Bug #21031	Alter table to memory table defaults to hash indexes
Submitted:	13 Jul 2006 11:55	Modified:	26 Oct 2006 13:38
Reporter:	Bj?rn-Arne Jensen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	5.0.21	OS:	Any (any)
Assigned to:	Antony Curtis	CPU Architecture:	Any
Tags:	hash, indexes, memory table

Description:
Altering a table from MyISAM to MEMORY changes the indexes from B-Tree to Hash, which should not be the case IMHO.

How to repeat:
CREATE TABLE btree_test (
    id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  , data VARCHAR(32), INDEX test_i (data)
);

SHOW INDEX FROM btree_test;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| btree_test |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      | NULL    |
| btree_test |          1 | test_i   |            1 | data        | A         |        NULL |     NULL | NULL   | YES  | BTREE      | NULL    |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

CREATE TABLE hash_test like btree_test;
ALTER TABLE hash_test ENGINE=memory;
SHOW INDEX FROM hash_test;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| hash_test |          0 | PRIMARY  |            1 | id          | NULL      |           0 |     NULL | NULL   |      | HASH       | NULL    |
| hash_test |          1 | test_i   |            1 | data        | NULL      |           0 |     NULL | NULL   | YES  | HASH       | NULL    |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Suggested fix:
Alter table should take care about index type. Means b-tree should stay b-tree

Thank you for a problem report. Verififed just as described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE btree_test (
    ->     id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ->   , data VARCHAR(32), INDEX test_i (data)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show indexes from btree_test\G
*************************** 1. row ***************************
       Table: btree_test
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: btree_test
  Non_unique: 1
    Key_name: test_i
Seq_in_index: 1
 Column_name: data
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)

mysql> alter table btree_test engine=memory;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from btree_test\G
*************************** 1. row ***************************
       Table: btree_test
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: NULL
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: HASH
     Comment:
*************************** 2. row ***************************
       Table: btree_test
  Non_unique: 1
    Key_name: test_i
Seq_in_index: 1
 Column_name: data
   Collation: NULL
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: HASH
     Comment:
2 rows in set (0.01 sec)

I think, this is a bug. If it is intended behaviour, it should be clearly documented, both for MEMORY engine and for ALTER TABLE statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14407

ChangeSet@1.2291, 2006-10-25 14:59:14-07:00, acurtis@xiphis.org +3 -0
  Bug#21031
    "Alter table to memory table defaults to hash indexes"
    Don't expect different storage engine's default algorithms to be the same.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14408

ChangeSet@1.2291, 2006-10-25 15:22:12-07:00, acurtis@xiphis.org +5 -0
  Bug#21031
    "Alter table to memory table defaults to hash indexes"
    Don't expect different storage engine's default algorithms to be the same.

This is expected behavior. If you didn't specify the index type explicitly, it'll be changed to the default index type for a given storage engine. E.g. in

create table t1 (a int, b int, c int, key using hash (a), key using btree (b), key(c));

the first index will be hash (if supported), the second - btree (if supported), the third - default. In MyiSAM all three will be btree, if you alter it to MEMORY,  the first and third will be hash, the second will stay btree.