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.