Bug #8452 Fulltext Index
Submitted: 11 Feb 2005 19:07 Modified: 11 Feb 2005 22:55
Reporter: Jim N Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.1.9 OS:Windows (Win XP SP2)
Assigned to: Sergei Golubchik CPU Architecture:Any

[11 Feb 2005 19:07] Jim N
When I do a "load index into cache" on a table with a column with a fulltext index, it doesn't seem to load the fulltext index into memory.

I have a computer with 2 Gigs of memory and the fulltext index is about 1 Gig in size.  The table is 3 Gigs.

I think it will significantly improve the speed of fulltext queries if the index is preloaded in memory.

How to repeat:
Create a table with a column which has fulltext index.  Then do a "load index into cache <tablename>"

Nothing happens and nothing loads.  The hard disk doesn't seem to light up.  It should since the file is 1 Gig in size.

Suggested fix:
Make fulltext index load into memory.
[11 Feb 2005 20:27] Hartmut Holzgraefe
The key cache can only cache MyISAM BTree indexes.
It doesn't work with the fulltext or spatial index types
supported by MyISAM.

I agree that this is not properly documented and i think
that LOAD INDEX INTO CACHE should at least give
a warning when loading a non-supported index type is


should warn that 't' is not a cacheable index type.
[11 Feb 2005 22:55] Sergei Golubchik
No, FULLTEXT index should be cacheable. The real bug was that LOAD INDEX did not work at all.
Fixed in 4.1.10
[16 Feb 2005 0:42] Jim N
I downloaded version 4.1.10.  I have a fulltext index and do a "Load index into cache...".  It seems to be loading and I see the key buffer usage go up as expected.  However, when I perform a fulltext search using "MATCH" it doesn't seem to use the index loaded in memory.  It still takes a very, very long time to search.
[16 Feb 2005 2:10] Jim N
Here is some more information after testing on 4.1.10:

I am using "MySQL Administrator" for Windows and when I do the SQL fulltext search using "Match", the key cache hit performance percentage is 0% which means that the key index is not being used.

The good news that the 4.1.10 fix makes it load into memory.  It's just that the "Match" doesn't seem to use it.
[16 Feb 2005 4:15] Jim N
Ok, it seems to be pretty fast.  I was performing the query in phpMyAdmin, and for some reason it is slow there.  But it was fast when I did the query from a PHP page manually.