root@localhost mysqld5128 [(none)]> select version(); +---------------+ | version() | +---------------+ | 5.1.28-rc-log | +---------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [(none)]> use test; Database changed root@localhost mysqld5128 [test]> select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.10 sec) 1 row in set (0.11 sec) 1 row in set (0.10 sec) 1 row in set (0.16 sec) 1 row in set (0.17 sec) 1 row in set (0.17 sec) 1 row in set (0.17 sec) 1 row in set (0.17 sec) 1 row in set (0.16 sec) 1 row in set (0.17 sec) 1 row in set (0.16 sec) 1 row in set (0.16 sec) 1 row in set (0.17 sec) 1 row in set (0.16 sec) 1 row in set (0.17 sec) 1 row in set (0.18 sec) root@localhost mysqld5128 [test]> create table test like INFORMATION_SCHEMA.GLOBAL_VARIABLES; Query OK, 0 rows affected (0.00 sec) root@localhost mysqld5128 [test]> insert into test select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES; Query OK, 267 rows affected (0.23 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> select count(*) from test; +----------+ | count(*) | +----------+ | 267 | +----------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> show create table test; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '', `VARIABLE_VALUE` varchar(20480) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.04 sec) 1 row in set (0.05 sec) 1 row in set (0.05 sec) 1 row in set (0.07 sec) 1 row in set (0.06 sec) 1 row in set (0.05 sec) 1 row in set (0.07 sec) 1 row in set (0.06 sec) 1 row in set (0.07 sec) 1 row in set (0.05 sec) 1 row in set (0.06 sec) 1 row in set (0.06 sec) 1 row in set (0.06 sec) 1 row in set (0.07 sec) 1 row in set (0.07 sec) 1 row in set (0.05 sec) 1 row in set (0.06 sec) 1 row in set (0.05 sec) 1 row in set (0.06 sec) 1 row in set (0.06 sec) 1 row in set (0.06 sec) root@localhost mysqld5128 [test]> alter table test engine=myisam; Query OK, 267 rows affected (0.12 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.01 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.01 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> alter table test engine=innodb; Query OK, 267 rows affected (0.08 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.01 sec) 1 row in set (0.01 sec) 1 row in set (0.01 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.01 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.01 sec) 1 row in set (0.01 sec) 1 row in set (0.01 sec) 1 row in set (0.01 sec) root@localhost mysqld5128 [test]> alter table test engine=memory; Query OK, 267 rows affected (0.13 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.07 sec) 1 row in set (0.06 sec) 1 row in set (0.05 sec) 1 row in set (0.07 sec) root@localhost mysqld5128 [test]> alter table test add index (VARIABLE_VALUE); Query OK, 267 rows affected, 2 warnings (0.17 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> show warnings; +---------+------+----------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------+ | Warning | 1071 | Specified key was too long; max key length is 3072 bytes | | Warning | 1071 | Specified key was too long; max key length is 3072 bytes | +---------+------+----------------------------------------------------------+ 2 rows in set (0.00 sec) root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.08 sec) 1 row in set (0.07 sec) 1 row in set (0.06 sec) root@localhost mysqld5128 [test]> explain select * from test where VARIABLE_NAME = 'hostname'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 267 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> alter table test add index (VARIABLE_VALUE) using btree; ERROR 1114 (HY000): The table '#sql-2201_6' is full root@localhost mysqld5128 [test]> show global variables like 'tmp_table%'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | tmp_table_size | 16777216 | +-------------------+----------+ 4 rows in set (0.01 sec) root@localhost mysqld5128 [test]> show global variables like 'max_heap%'; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 16777216 | +---------------------+----------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> alter table test add index (VARIABLE_VALUE(128)) using btree; Query OK, 267 rows affected (0.17 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.09 sec) 1 row in set (0.08 sec) 1 row in set (0.08 sec) root@localhost mysqld5128 [test]> explain select * from test where VARIABLE_NAME = 'hostname'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 267 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> show create table test; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '', `VARIABLE_VALUE` varchar(20480) DEFAULT NULL, KEY `VARIABLE_VALUE` (`VARIABLE_VALUE`(1024)), KEY `VARIABLE_VALUE_2` (`VARIABLE_VALUE`(128)) USING BTREE ) ENGINE=MEMORY DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> alter table test drop index VARIABLE_VALUE; Query OK, 267 rows affected (0.17 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> show create table test; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '', `VARIABLE_VALUE` varchar(20480) DEFAULT NULL, KEY `VARIABLE_VALUE_2` (`VARIABLE_VALUE`(128)) USING BTREE ) ENGINE=MEMORY DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> explain select * from test where VARIABLE_NAME = 'hostname'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 267 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.07 sec) 1 row in set (0.05 sec) 1 row in set (0.06 sec) root@localhost mysqld5128 [test]> select max(length(VARIABLE_VALUE)) from test; +-----------------------------+ | max(length(VARIABLE_VALUE)) | +-----------------------------+ | 53 | +-----------------------------+ 1 row in set (0.10 sec) root@localhost mysqld5128 [test]> alter table test change VARIABLE_VALUE VARIABLE_VALUE varchar(255); Query OK, 267 rows affected (0.10 sec) Records: 267 Duplicates: 0 Warnings: 0 root@localhost mysqld5128 [test]> show create table test; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '', `VARIABLE_VALUE` varchar(255) DEFAULT NULL, KEY `VARIABLE_VALUE_2` (`VARIABLE_VALUE`(128)) USING BTREE ) ENGINE=MEMORY DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.01 sec) root@localhost mysqld5128 [test]> explain select * from test where VARIABLE_NAME = 'hostname'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 267 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> select * from test where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.01 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> explain select * from test where VARIABLE_NAME = 'hostname'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 267 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@localhost mysqld5128 [test]> exit