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 33 Server version: 5.0.45-community MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table test (test_id int(11) auto_increment primary key, num1 int(11) not null, num2 int(11) not null, text varchar(255) , key (num1), key(num2) ) ENGINE=InnoDb;Query OK, 0 rows affected (0.01 sec) mysql> insert into test (num1,num2, text) select ifnull(table_rows%avg_row_length,0), ifnull(table_rows%max_data_length,0), concat(table_schema,table_name) from information_schema.tables join (select 1 a from information_schema.TABLES) a; Query OK, 416025 rows affected (11.41 sec) Records: 416025 Duplicates: 0 Warnings: 0 mysql> show indexes from test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | test | 0 | PRIMARY | 1 | test_id | A | 400002 | NULL | NULL | | BTREE | | | test | 1 | num1 | 1 | num1 | A | 400002 | NULL | NULL | | BTREE | | | test | 1 | num2 | 1 | num2 | A | 400002 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.01 sec) mysql> show indexes from test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | test | 0 | PRIMARY | 1 | test_id | A | 409685 | NULL | NULL | | BTREE | | | test | 1 | num1 | 1 | num1 | A | 409685 | NULL | NULL | | BTREE | | | test | 1 | num2 | 1 | num2 | A | 409685 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec) mysql> show indexes from test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | test | 0 | PRIMARY | 1 | test_id | A | 423717 | NULL | NULL | | BTREE | | | test | 1 | num1 | 1 | num1 | A | 423717 | NULL | NULL | | BTREE | | | test | 1 | num2 | 1 | num2 | A | 423717 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.00 sec) mysql> select * from information_schema.statistics where table_schema = 'test' and table_name ='test'; +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+ | NULL | test | test | 0 | test | PRIMARY | 1 | test_id | A | 413045 | NULL | NULL | | BTREE | | | NULL | test | test | 1 | test | num1 | 1 | num1 | A | 413045 | NULL | NULL | | BTREE | | | NULL | test | test | 1 | test | num2 | 1 | num2 | A | 413045 | NULL | NULL | | BTREE | | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+ 3 rows in set (0.01 sec) mysql> select * from information_schema.statistics where table_schema = 'test' and table_name ='test'; +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+ | NULL | test | test | 0 | test | PRIMARY | 1 | test_id | A | 416207 | NULL | NULL | | BTREE | | | NULL | test | test | 1 | test | num1 | 1 | num1 | A | 416207 | NULL | NULL | | BTREE | | | NULL | test | test | 1 | test | num2 | 1 | num2 | A | 416207 | NULL | NULL | | BTREE | | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+ 3 rows in set (0.00 sec) mysql>