Description:
After table creation and loading data ANALYZE TABLE shows table is up to date and does nothing:
mysql> analyze table transl;
+-------------+---------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+-----------------------------+
| test.transl | analyze | status | Table is already up to date |
+-------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)
mysql> explain select a.refseq_id, b.refseq_id from transl a, transl b
-> where a.interaction_id=b.interaction_id and a.refseq_id!=b.refseq_id ;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | a | ALL | tr_id,tr_rs | NULL | NULL | NULL | 27402 | |
| 1 | SIMPLE | b | ALL | tr_id | NULL | NULL | NULL | 20552 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
2 rows in set (0.03 sec)
After executing either CHECK TABLE or ALTER TABLE statements EXPLAIN shows the following:
mysql> check table transl;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.transl | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.20 sec)
mysql> explain select a.refseq_id, b.refseq_id from transl a, transl b
-> where a.interaction_id=b.interaction_id and a.refseq_id!=b.refseq_id ;
+----+-------------+-------+------+---------------+-------+---------+-----------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-----------------------+-------+-------------+
| 1 | SIMPLE | b | ALL | tr_id | NULL | NULL | NULL | 27402 | |
| 1 | SIMPLE | a | ref | tr_id,tr_rs | tr_id | 4 | test.b.interaction_id | 1 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-----------------------+-------+----------
How to repeat:
1. unpack mysqlbug.tar.gz
2. use settings from my.cnf file
3. run mysqlbug.sh and see the output.
Description: After table creation and loading data ANALYZE TABLE shows table is up to date and does nothing: mysql> analyze table transl; +-------------+---------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-------------+---------+----------+-----------------------------+ | test.transl | analyze | status | Table is already up to date | +-------------+---------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> explain select a.refseq_id, b.refseq_id from transl a, transl b -> where a.interaction_id=b.interaction_id and a.refseq_id!=b.refseq_id ; +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | a | ALL | tr_id,tr_rs | NULL | NULL | NULL | 27402 | | | 1 | SIMPLE | b | ALL | tr_id | NULL | NULL | NULL | 20552 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 2 rows in set (0.03 sec) After executing either CHECK TABLE or ALTER TABLE statements EXPLAIN shows the following: mysql> check table transl; +-------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+-------+----------+----------+ | test.transl | check | status | OK | +-------------+-------+----------+----------+ 1 row in set (0.20 sec) mysql> explain select a.refseq_id, b.refseq_id from transl a, transl b -> where a.interaction_id=b.interaction_id and a.refseq_id!=b.refseq_id ; +----+-------------+-------+------+---------------+-------+---------+-----------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------+---------+-----------------------+-------+-------------+ | 1 | SIMPLE | b | ALL | tr_id | NULL | NULL | NULL | 27402 | | | 1 | SIMPLE | a | ref | tr_id,tr_rs | tr_id | 4 | test.b.interaction_id | 1 | Using where | +----+-------------+-------+------+---------------+-------+---------+-----------------------+-------+---------- How to repeat: 1. unpack mysqlbug.tar.gz 2. use settings from my.cnf file 3. run mysqlbug.sh and see the output.