Bug #11160 table is marked as up to date, but it's not true. as a result index is not used
Submitted: 8 Jun 2005 9:32 Modified: 18 Sep 2008 6:28
Reporter: Victoria Reznichenko Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.1.12 OS:Linux (linux)
Assigned to: Sveta Smirnova CPU Architecture:Any

[8 Jun 2005 9:32] Victoria Reznichenko
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.
[18 Sep 2008 6:28] Sveta Smirnova
Bug is not repeatable with version 4.1.23.