Confirmed with PS 5.6 nilnandan@desktop:~/Downloads$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 40 Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f Copyright (c) 2009-2015 Percona LLC and/or its affiliates Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb; ERROR 1050 (42S01): Table 't1' already exists mysql> mysql> drop table t1 -> -> ; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> create table t1(f1 int, f2 int, key(f1), key f1_2 (f1, f2)) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1(f1) values (1),(2),(3),(4),(5),(6); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into t1 (f1) select f1 from t1; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into t1 (f1) select f1 from t1; Query OK, 12 rows affected (0.00 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> insert into t1 (f1) select f1 from t1; Query OK, 24 rows affected (0.01 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql> insert into t1 (f1) select f1 from t1; Query OK, 48 rows affected (0.00 sec) Records: 48 Duplicates: 0 Warnings: 0 mysql> insert into t1 (f1) select f1 from t1; Query OK, 96 rows affected (0.00 sec) Records: 96 Duplicates: 0 Warnings: 0 mysql> insert into t1 (f1) select f1 from t1; Query OK, 192 rows affected (0.00 sec) Records: 192 Duplicates: 0 Warnings: 0 mysql> insert into t1 (f1) select f1 from t1; Query OK, 384 rows affected (0.01 sec) Records: 384 Duplicates: 0 Warnings: 0 mysql> update t1 set f2=rand(); Query OK, 768 rows affected, 1 warning (0.01 sec) Rows matched: 768 Changed: 768 Warnings: 1 mysql> mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> analyze table t1; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show indexes from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t1 | 1 | f1 | 1 | f1 | A | 12 | NULL | NULL | YES | BTREE | | | | t1 | 1 | f1_2 | 1 | f1 | A | 12 | NULL | NULL | YES | BTREE | | | | t1 | 1 | f1_2 | 2 | f2 | A | 24 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1; +--------------------+--------------------+------------------------+----------+ | count(distinct f1) | count(distinct f2) | count(distinct f1, f2) | count(*) | +--------------------+--------------------+------------------------+----------+ | 6 | 2 | 12 | 768 | +--------------------+--------------------+------------------------+----------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats; +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx01 | 3 | 2 | db | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx02 | 29 | 2 | db,tbl | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx03 | 109 | 2 | db,tbl,chunk | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_leaf_pages | 2 | NULL | Number of leaf pages in the index | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | size | 3 | NULL | Number of pages in the index | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx01 | 49 | 1 | ts | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx02 | 50 | 1 | ts,db | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx03 | 76 | 1 | ts,db,tbl | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx04 | 109 | 1 | ts,db,tbl,chunk | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | size | 1 | NULL | Number of pages in the index | | source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_diff_pfx01 | 0 | 1 | id | | source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | size | 1 | NULL | Number of pages in the index | | test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_diff_pfx01 | 10 | 1 | DB_ROW_ID | | test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | size | 1 | NULL | Number of pages in the index | | test | t1 | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | n_diff_pfx01 | 768 | 3 | DB_ROW_ID | | test | t1 | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | n_leaf_pages | 3 | NULL | Number of leaf pages in the index | | test | t1 | GEN_CLUST_INDEX | 2015-08-14 13:29:37 | size | 4 | NULL | Number of pages in the index | | test | t1 | f1 | 2015-08-14 13:29:37 | n_diff_pfx01 | 6 | 1 | f1 | | test | t1 | f1 | 2015-08-14 13:29:37 | n_diff_pfx02 | 768 | 1 | f1,DB_ROW_ID | | test | t1 | f1 | 2015-08-14 13:29:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | f1 | 2015-08-14 13:29:37 | size | 1 | NULL | Number of pages in the index | | test | t1 | f1_2 | 2015-08-14 13:29:37 | n_diff_pfx01 | 6 | 1 | f1 | | test | t1 | f1_2 | 2015-08-14 13:29:37 | n_diff_pfx02 | 12 | 1 | f1,f2 | | test | t1 | f1_2 | 2015-08-14 13:29:37 | n_diff_pfx03 | 768 | 1 | f1,f2,DB_ROW_ID | | test | t1 | f1_2 | 2015-08-14 13:29:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t1 | f1_2 | 2015-08-14 13:29:37 | size | 1 | NULL | Number of pages in the index | | test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID | | test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | size | 1 | NULL | Number of pages in the index | | test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID | | test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | size | 1 | NULL | Number of pages in the index | | test | testb | PRIMARY | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | a | | test | testb | PRIMARY | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | PRIMARY | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | e | | test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx02 | 0 | 1 | e,a | | test | testb | fk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | fk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | uk1 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | b | | test | testb | uk1 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | uk1 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | uk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | c | | test | testb | uk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | uk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | uk3 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | d | | test | testb | uk3 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | uk3 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 51 rows in set (0.00 sec) mysql> mysql> alter table t1 engine=myisam; Query OK, 768 rows affected (0.01 sec) Records: 768 Duplicates: 0 Warnings: 0 mysql> mysql> analyze table t1; +---------+---------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+-----------------------------+ | test.t1 | analyze | status | Table is already up to date | +---------+---------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> show indexes from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t1 | 1 | f1 | 1 | f1 | A | 6 | NULL | NULL | YES | BTREE | | | | t1 | 1 | f1_2 | 1 | f1 | A | 6 | NULL | NULL | YES | BTREE | | | | t1 | 1 | f1_2 | 2 | f2 | A | 12 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> select count(distinct f1), count(distinct f2), count(distinct f1, f2), count(*) from t1; +--------------------+--------------------+------------------------+----------+ | count(distinct f1) | count(distinct f2) | count(distinct f1, f2) | count(*) | +--------------------+--------------------+------------------------+----------+ | 6 | 2 | 12 | 768 | +--------------------+--------------------+------------------------+----------+ 1 row in set (0.00 sec) mysql> select * from mysql.innodb_index_stats; +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx01 | 3 | 2 | db | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx02 | 29 | 2 | db,tbl | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_diff_pfx03 | 109 | 2 | db,tbl,chunk | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | n_leaf_pages | 2 | NULL | Number of leaf pages in the index | | percona | checksums | PRIMARY | 2015-08-12 13:07:19 | size | 3 | NULL | Number of pages in the index | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx01 | 49 | 1 | ts | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx02 | 50 | 1 | ts,db | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx03 | 76 | 1 | ts,db,tbl | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_diff_pfx04 | 109 | 1 | ts,db,tbl,chunk | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | percona | checksums | ts_db_tbl | 2015-08-12 13:07:19 | size | 1 | NULL | Number of pages in the index | | source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_diff_pfx01 | 0 | 1 | id | | source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | source_db | altertest | PRIMARY | 2015-07-20 12:44:07 | size | 1 | NULL | Number of pages in the index | | test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_diff_pfx01 | 10 | 1 | DB_ROW_ID | | test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | nil | GEN_CLUST_INDEX | 2015-08-14 13:22:46 | size | 1 | NULL | Number of pages in the index | | test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID | | test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t2 | GEN_CLUST_INDEX | 2015-08-11 11:57:31 | size | 1 | NULL | Number of pages in the index | | test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID | | test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | t3 | GEN_CLUST_INDEX | 2015-08-11 11:57:37 | size | 1 | NULL | Number of pages in the index | | test | testb | PRIMARY | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | a | | test | testb | PRIMARY | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | PRIMARY | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | e | | test | testb | fk2 | 2015-07-24 09:45:39 | n_diff_pfx02 | 0 | 1 | e,a | | test | testb | fk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | fk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | uk1 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | b | | test | testb | uk1 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | uk1 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | uk2 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | c | | test | testb | uk2 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | uk2 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | | test | testb | uk3 | 2015-07-24 09:45:39 | n_diff_pfx01 | 0 | 1 | d | | test | testb | uk3 | 2015-07-24 09:45:39 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | testb | uk3 | 2015-07-24 09:45:39 | size | 1 | NULL | Number of pages in the index | +---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 39 rows in set (0.00 sec) mysql> mysql>