After I tuned up the search engine of http://databg.svilen.com I made a full update of the database.
The new rewritten crawl script is doing fine. I am able to index all files for around 5-6 days.
Here are my advices about MySQL FULLTEXT SEARCH optimizations based on my experience with the Free Search:
1) try using defult "my-huge.cnf" as mysql config file. It is tuned for server with lots of RAM 1-2GB
2) play some with "key_buffer" and "key_buffer_size". These values are the most important. I've set them to:
key_buffer = 576M
key_buffer_size = 448M
3) turn on mysql query cache:
query_cache_size = 64M
4) try to avoid counting the results from a given fulltext search:
exp.: SELECT count(*) FROM table WHERE MATCH(filename) AGAINST('+keyword' IN BOOLEAN MODE) > 0;
5) remove any unnecessary characters such as dot, comma from the searched keywords. Remember that mysql fulltext index only words!!!
6) lower your index size as much as possible. Means that you have to leave only necessary indexes! This is critical as mysql tries to load indexes in RAM before searching and if you have less free RAM memory than your index file you get slow queries
7) use persistent connections!
8) use prepared statements if possible. PHP5's PDO supports prepared statements (http://bg.php.net/manual/en/ref.pdo.php)
That's it! :)
Although my server gets usually loaded, but I think it's because of the traffic. It's pretty much. WOW!