| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.17-BK, 5.0.15 | OS: | Linux (Linux, Windows XP) |
| Assigned to: | Sergey Vlasenko | CPU Architecture: | Any |
[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.
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