MySQL Bugs: #8452: Fulltext Index

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:	None
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

Description:
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.

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
requested:

  CREATE TABLE ft (t TEXT, FULLTEXT KEY t));
  LOAD INDEX INTO CACHE ft (t);  

should warn that 't' is not a cacheable index type.

No, FULLTEXT index should be cacheable. The real bug was that LOAD INDEX did not work at all.
Fixed in 4.1.10

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.

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.

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.