Re: [4.0.14] Small bug with OPTIMIZE/ANALYZE and FULLTEXT index

Hi Sinisa!

Thanks for replying, but... you can't see that this is a bug? :-) The
behavior is *NOT* consistent with *any* other version of MySQL I've
*ever* used OPTIMIZE/ANALYZE on with MyISAM tables. If it's not a bug,
then all the other versions must have the bug. :-)

I thought it would a simple bug report. But now I have investigated
further to give more details about the problem. I hope you can see the
bug. ;-) The problem seems to be, as I discovered with myisamchk, that
the table is not remembering that it was analyzed when it has a FULLTEXT
index.

----- Original Message -----
From: "Sinisa Milivojevic" <[email protected]>
Sent: Monday, August 25, 2003 5:56 AM

> HI!
>
> First of all there is no need to run both OPTIMIZE / ANALYZE as first
> will do the second.

Yes, I'm aware of that. I included both to show that they're both messed
up.


> Next, with dynamic records OPTIMIZE always has something to do.

Not if the table *HAS NOT* changed! :-) And ANALYZE doesn't care if it's
fixed or dynamic since it only works on the indexes, right? Anyway, the
fact that my example used dynamic records is irrelevant. I'll use a CHAR
this time, and the results will be the same with fixed records. ;-)

OK, follow along here:

CREATE TABLE test (text CHAR(255) NOT NULL, FULLTEXT (text));
INSERT INTO test VALUES ('Some words.');

Let's see what myisamchk says at this point:

myisamchk -dv path/to/db/test
...
Status: open,changed
...

Marked as changed of course. Now...

OPTIMIZE TABLE test;
-- Msg_text: OK

Fine. Now let's see myisamchk:

myisamchk -dv path/to/db/test
...
Status: checked,analyzed,sorted index pages
...

Notice "analyzed." And when we run OPTIMIZE again:

OPTIMIZE TABLE test;
-- Msg_text: Table is already up to date

This is what should happen because the table is no longer marked as
changed and it's been analyzed. This shows that, for some reason, it
works correctly with a small amount of text/words in the column
(table?). Now to add a bit more text:

UPDATE test SET text='Four words test text.';

Checking myisamchk again:

myisamchk -dv path/to/db/test
...
Status: open,changed,sorted index pages
...

All normal so far. Now to OPTIMIZE it:

OPTIMIZE TABLE test;
Msg_text: OK

All normal still. What does myisamchk say?:

myisamchk -dv path/to/db/test
...
Status: checked,sorted index pages
...

Uh oh! Where's analyzed that was there last time we ran OPTIMIZE? :-)
What will happen if we run OPTIMIZE again?

OPTIMIZE TABLE test;
Msg_text: OK

No, no, no! That supposed to say "Table is already up to date." And
myisamchk again:

myisamchk -dv path/to/db/test
...
Status: checked,sorted index pages
...

See, it still doesn't remember that it's been analyzed. This bug means
that on my 3 million row table that it analyzes the table again for 5
minutes when nothing has changed! It should know that it's up to date.
This works *CORRECTLY* in 4.0.12 and 4.1.0-alpha (Status includes
analyzed), just like it does in every other version I've used except
4.0.14.

Let's drop the FULLTEXT index:

ALTER TABLE test DROP INDEX text;

myisamchk still indicates the same now... until we OPTIMIZE:

OPTIMIZE TABLE test;
Msg_text: OK

Then:

myisamchk -dv path/to/db/test
...
Status: checked,analyzed,sorted index pages
...

Bingo! It's now correct again, and OPTIMIZE knows this:

OPTIMIZE TABLE test;
Msg_text: Table is already up to date


This is the same behavior as in my 3M row table that has other
columns/indexes in it. The problem of not being marked as analyzed goes
away if the FULLTEXT index is removed or by using 4.0.12 etc. --
anything but 4.0.14.

I hope this is enough information. :-)

Regards,
Matt



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]