MySQL Bugs: #15932: why can't indexes updated?

Bug #15932	why can't indexes updated?
Submitted:	22 Dec 2005 9:31	Modified:	22 Jan 2006 13:41
Reporter:	zhenxing zhai	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.17	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
I have a table weblogentry,define and index status as follows: 

mysql> desc weblogentry; 
+---------------+--------------+------+-----+---------------------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+---------------+--------------+------+-----+---------------------+----------------+ 
| ANCHOR | varchar(255) | NO | | | | 
| TITLE | varchar(255) | NO | MUL | | | 
| pubtime | timestamp | YES | | CURRENT_TIMESTAMP | | 
| updatetime | timestamp | YES | | 0000-00-00 00:00:00 | | 
| PUBLISHENTRY | tinyint(4) | NO | MUL | 0 | | 
| ALLOWCOMMENTS | tinyint(4) | NO | MUL | 0 | | 
| EXTRATEXT2 | varchar(255) | YES | | NULL | | 
| KEYWORDS | varchar(255) | YES | | NULL | | 
| ISPASS | tinyint(4) | YES | MUL | NULL | | 
| id | int(11) | NO | PRI | NULL | auto_increment | 
| WEBSITEID | int(11) | YES | MUL | NULL | | 
| CATEGORYID | int(11) | YES | MUL | NULL | | 
| TEXT | text | YES | | NULL | | 
| EXTRATEXT | text | YES | | NULL | | 
| userid | int(11) | YES | MUL | 0 | | 
+---------------+--------------+------+-----+---------------------+----------------+ 
15 rows in set (0.05 sec) 

mysql> show index from weblogentry; 
+-------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
+-------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 
| weblogentry | 0 | PRIMARY | 1 | id | A | 1295377 | NULL | NULL | | BTREE | | 
| weblogentry | 1 | idx_weblogentry_title | 1 | TITLE | A | 647688 | NULL | NULL | | BTREE | | 
| weblogentry | 1 | idx_weblogentry_publishentry | 1 | PUBLISHENTRY | A | 11 | NULL | NULL | | BTREE | | 
| weblogentry | 1 | idx_weblogentry_allowcomments | 1 | ALLOWCOMMENTS | A | 2 | NULL | NULL | | BTREE | | 
| weblogentry | 1 | idx_weblogentry_ispass | 1 | ISPASS | A | 3 | NULL | NULL | YES | BTREE | | 
| weblogentry | 1 | idx_weblogentry_websiteid | 1 | WEBSITEID | A | 259075 | NULL | NULL | YES | BTREE | | 
| weblogentry | 1 | idx_weblogentry_categoryid | 1 | CATEGORYID | A | 323844 | NULL | NULL | YES | BTREE | | 
| weblogentry | 1 | idx_weblogentry_userid | 1 | userid | A | 259075 | NULL | NULL | YES | BTREE | | 
| weblogentry | 1 | idx_weblogentry_pub_ispass_id | 1 | PUBLISHENTRY | A | 11 | NULL | NULL | | BTREE | | 
| weblogentry | 1 | idx_weblogentry_pub_ispass_id | 2 | ISPASS | A | 24 | NULL | NULL | YES | BTREE | | 
| weblogentry | 1 | idx_weblogentry_pub_ispass_id | 3 | id | A | 1295377 | NULL | NULL | | BTREE | | 
+-------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 
11 rows in set (0.00 sec) 

next,I create another table weblogentry_new ,create procedure as follows: 

1、show create table weblogentry 

2 copy create table statment and execute, 

CREATE TABLE `weblogentry_new` ( 
`ANCHOR` varchar(255) NOT NULL default '', 
`TITLE` varchar(255) NOT NULL default '', 
`pubtime` timestamp NOT NULL default CURRENT_TIMESTAMP, 
`updatetime` timestamp NOT NULL default '0000-00-00 00:00:00', 
`PUBLISHENTRY` tinyint(4) NOT NULL default '0', 
`ALLOWCOMMENTS` tinyint(4) NOT NULL default '0', 
`EXTRATEXT2` varchar(255) default NULL, 
`KEYWORDS` varchar(255) default NULL, 
`ISPASS` tinyint(4) default NULL, 
`id` int(11) NOT NULL auto_increment, 
`WEBSITEID` int(11) default NULL, 
`CATEGORYID` int(11) default NULL, 
`TEXT` text, 
`EXTRATEXT` text, 
`userid` int(11) default '0', 
PRIMARY KEY (`id`), 
KEY `idx_weblogentry_title` (`TITLE`), 
KEY `idx_weblogentry_publishentry` (`PUBLISHENTRY`), 
KEY `idx_weblogentry_allowcomments` (`ALLOWCOMMENTS`), 
KEY `idx_weblogentry_ispass` (`ISPASS`), 
KEY `idx_weblogentry_websiteid` (`WEBSITEID`), 
KEY `idx_weblogentry_categoryid` (`CATEGORYID`), 
KEY `idx_weblogentry_userid` (`userid`), 
KEY `idx_weblogentry_pub_ispass_id` (`PUBLISHENTRY`,`ISPASS`,`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=gbk 

3 insert into weblogentry_new select * from weblogentry; 

4 show index from weblogentry_new 

mysql> show index from weblogentry_new; 
+-----------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
+-----------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 
| weblogentry_new | 0 | PRIMARY | 1 | id | A | 1295377 | NULL | NULL | | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_title | 1 | TITLE | A | NULL | NULL | NULL | | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_publishentry | 1 | PUBLISHENTRY | A | NULL | NULL | NULL | | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_allowcomments | 1 | ALLOWCOMMENTS | A | NULL | NULL | NULL | | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_ispass | 1 | ISPASS | A | NULL | NULL | NULL | YES | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_websiteid | 1 | WEBSITEID | A | NULL | NULL | NULL | YES | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_categoryid | 1 | CATEGORYID | A | NULL | NULL | NULL | YES | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_userid | 1 | userid | A | NULL | NULL | NULL | YES | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_pub_ispass_id | 1 | PUBLISHENTRY | A | NULL | NULL | NULL | | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_pub_ispass_id | 2 | ISPASS | A | NULL | NULL | NULL | YES | BTREE | | 
| weblogentry_new | 1 | idx_weblogentry_pub_ispass_id | 3 | id | A | NULL | NULL | NULL | | BTREE | | 
+-----------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 

column Cardinality of all indexs is null expect primary key ,I don't know if these indexes have been updated? if I need create these indexes repead?if haven't been updated,why only primary key has been updated?

How to repeat:
any two tables will  repeat it

Suggested fix:
I hope index will be updated when do like 'insert into table1 select tableb'

Thank you for a problem report. Looks like non-unique indexes are not always updated during 

insert into weblogentry_new select * from weblogentry;

because of the same bulk-insert optimization that is performed for LOAD DATA INFILE... I am looking for the description to give you the URL.

Please, try to perform ANALYZE TABLE weblogentry_new after INSERT ... SELECT... Your indexes information should become correct after that.

The problem can be demonstrated using only one table:

mysql> create table t1 (c1 int auto_increment primary key, c2 char(10), key c2_k
ey (c2)) engine = MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1(c2) values ('aaa');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t1(c2) values ('aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(c2) values ('aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(c2) values ('bbb');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(c2) select c2 from t1;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) select c2 from t1;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) select c2 from t1;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) select c2 from t1;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) select c2 from t1;
Query OK, 64 rows affected (0.02 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into t1(c2) select c2 from t1;
Query OK, 128 rows affected (0.02 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | c1          | A         |   256 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | c2_key   |            1 | c2          | A         |  NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows 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 index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | c1          | A         |   256 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | c2_key   |            1 | c2          | A         |     2 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".