Bug #14902 ANALYZE TABLE fails to recognize up-to-date tables
Submitted: 12 Nov 2005 18:26 Modified: 9 Dec 2005 0:31
Reporter: Peter Schäfer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.17-BK, 5.0.15 OS:Linux (Linux, Windows XP)
Assigned to: Bugs System CPU Architecture:Any

[12 Nov 2005 18:26] Peter Schäfer
Description:
ANALYZE TABLE fails to recognize if a table is up-to-date.
Repeated calls to ANALYZE TABLE will rebuild the statistics, even if the table contents was not modified.

Presence of a fulltext index is a prerequisite.

How to repeat:
create table test (a mediumtext, fulltext key key1(a)) charset utf8 collate utf8_general_ci

insert into test values ('Hello')

analyze table test

  Table      , Op       , Msg_type, Msg_text
  'jose.test', 'analyze', 'status', 'OK'    

analyze table test

  Table      , Op       , Msg_type, Msg_text
  'jose.test', 'analyze', 'status', 'OK'    

analyze table test

  Table      , Op       , Msg_type, Msg_text
  'jose.test', 'analyze', 'status', 'OK'    

etc...

Suggested fix:
ANALYZE TABLE must recognize if a table is up-to-date and not build statistics
[13 Nov 2005 11:29] Valeriy Kravchuk
Thank you for a problem report. Verified just as described on 5.0.17-BK (ChangeSet@1.1967, 2005-11-11 13:39:46+04:00,...) on Linux:

mysql> create table test (a mediumtext, fulltext key key1(a)) charset utf8 collate
    -> utf8_general_ci;
Query OK, 0 rows affected (0,01 sec)

mysql> insert into test values('Hello');
Query OK, 1 row affected (0,00 sec)

mysql> analyze table test;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.test | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0,00 sec)

mysql> analyze table test;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.test | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0,00 sec)

mysql> create table test2 as select * from test;
Query OK, 1 row affected (0,01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> analyze table test2;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| test.test2 | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0,00 sec)

mysql> analyze table test2;
+------------+---------+----------+-----------------------------+
| Table      | Op      | Msg_type | Msg_text                    |
+------------+---------+----------+-----------------------------+
| test.test2 | analyze | status   | Table is already up to date |
+------------+---------+----------+-----------------------------+
1 row in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,00 sec)

And in the manual one may read (http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html):

"If the table has not changed since the last ANALYZE TABLE statement, the table is not analyzed again."

Looks like a bug or the behaviour that should be explicitly described in the manual.
[23 Nov 2005 15:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32604
[1 Dec 2005 20:52] Sergey Vlasenko
Fix is available in 5.0.17 & 5.1.4-alpha
[9 Dec 2005 0:31] Paul DuBois
Noted in 5.0.17, 5.1.4 changelogs.