Hi,
I got the following values: key_buffer_size = 256M myisam max extra sort file size = 8000M myisam max sort file size = 8000M myisam sort buffer size = 128M
But that big table (MYD = 2397 MB), rows = 5 355 866 still won't index in full text.
Disk space is enough (25GB of free space), no error reported in the log.
Using MySQL 4.0.14, doesn't work better with 4.0.20 (running under Win32).
Where should I look at?
Vincent
Sergei Golubchik wrote:
Hi!
First - please use mailing lists for these questions, don't write to me directly. You mail can be filtered in some obscure folder and I won't see it for months. Also, there are many experienced users on the list, so you can get a reply faster.
On Jun 20, Vincent Bouret wrote:
Hi,
I have been using the great fulltext capabilities of MySQL extensively over the past year without having any problem. I just got a strange problem, the fulltext index creation on very huge sets of data doesn't seem to work.
I have switched from 4.0.14 to 4.0.20 and rebuilt the database from
scratch. I'm adding a lot of data on different tables (4 TEXT
fields/table). At the end, I issue a ALTER TABLE 'tablename' ADD
FULLTEXT ('field1'), ADD FULLTEXT ('field2'), ADD FULLTEXT ('field3'),
ADD FULLTEXT ('field4');, this SQL command worked just fine for MYD as
large as 495MB. Strangely, this same command doesn't work with table
with MYD size of 776MB (2.5 million rows), 2.4GB (5.3 million rows),
900MB (2.1 million rows). On the first 776MB table, I can issue a
ALTER TABLE 'table1' ADD FULLTEXT ('field1'); and one fulltext index
is created, but I cannot issue one more: ALTER TABLE 'table1' ADD
FULLTEXT ('field2'); on a different field. I see three temporary files
The way MySQL works, when you issue ALTER TABLE the table is rebuild from scratch, so when you add *second* fulltext index with ALTER TABLE, in fact, the first index is being rebuilt too. Thus you have no benefits in adding indexes one by one - always do it in one ALTER TABLE.
being created, the MYI grows larger and larger and every temp files
disapear, and no fulltext index is created on field2. Even worse, on
the 900MB table, I cannot issue even a single ALTER TABLE 'table1' ADD
FULLTEXT ('field1');
Check myisam_... server variables (especially ...sort_file... related) Check SHOW PROCESSLIST duing the ALTER process, error log, and available disk space.
But please, direct your replies to the list!
Strangely, before rebuilding the database from scratch, I was doing
daily inserts on the content of these tables and the fulltext index
was updated sucessfully. The the MYD were the same size as they are
currently, but I think the index first got created with much smaller
tables.
When you create indexes on the existing data and when you add data to the index MySQL uses different algoritms to create/modify indexes. Thus is the first one - in ALTER TABLE - has a bug, you won't trigger it with regular updates.
Regards, Sergei
