After a few days of playing around I finally was able to come up with a solution:
Even though it says it will be much faster to populate the table, then add the fulltext index, it appears the opposite is true for large tables. I built an empty copy of the table I wanted to index, added the fulltext indexes and then did a series of statemens like this: insert into table_new select * from table_old where id > x and id <= y; but before I ran thoes statements I set key_buffer_size=250M (the larger the better probably). This was run on 3.23.47-nt and it took a little under 4 hours to copy and index the whole table into the new table. Its about a 600 meg table, containing 3 million rows and I fulltext indexed 2 of the columns which produced about a 500 meg fulltext index. by way of comparison, I tried to create the index on the pre-populated table several times, but gave up after letting it run overnight. So obiously the 4 hours is much more attractive :) -- Brian Bray Brian Bray wrote: > I have a smaller database but am still unable to fulltext index one of > the tables that is only about 600mb and has rougly 3.5 million rows. > > I tried both 3.23.47 and 4.0.1 and in both cases it was about the same. > I watched the temporary table that it was building and in both cases it > got up to about 550 megs (the temporary new index file was about the > same size) and then the hard drive just thrashed all night and the > temporary tables didnt grow one byte. I set the myisam_sort_buffer_size > up to 128mb but that didnt seem to help any. > > I did notice though that 4.0.1 got to the "thrash" point much quicker > than 3.23. > > I looked though all the variables, but I couldnt find any other > variables that looked like they affected index rebuilding. > > Any help would be appreciated, > > Thanks, > Brian Bray > > > Sergei Golubchik wrote: > >> Hi! >> >> On Jan 21, Steve Rapaport wrote: >> >>> Okay, does anyone know how long this will take? >>> 22 million records, fulltext index on a single field, first 40 chars. >>> record length 768 chars, variable. >>> >>> It's been running for 2 days, processlist quotes time at around 100000. >>> Index file still growing occasionally, up to 3Gb. >>> >>> Should I let it continue or give up? Will this take another 2 days? >>> another week? Anyone else with experience to get a rule of thumb? >>> >>> I'm using mysql 3.23.37 on a dual processor intel Redhat, 700Mhz, 1G >>> ram. >>> >> >> on your hardware it should index about 15Kb/sec. >> >> I'd recommend you to install MySQL-4.0.1, index your tables, >> and copy them back to 3.23.37 (if you prefer to use 3.23 branch). >> >> MySQL 4.0.x should create fulltext index with up to 1Mb/sec >> (benchmarks are hardware dependent, of course). >> >> Regards, >> Sergei >> >> > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
