Re: FULLTEXT Search performance

[James Day]

If you can, allocating a key_buffer_size of 700MB and preloading it with load index will help. Add more for the other things you'll use. Might want to use the MySQL 4.1 and later feature to load into specific index areas to ensure that the full-text index stays in RAM if you find that it seems to be replaced. In the case of this count you'd also want enough in operating system cache to hold the complete full-text table if possible. My own experience with fulltext search suggests that what is taking the time is the retrieval of the records, not the search itself, so favoring OS cache for the data records might be of more use than helping the index be cached..

You might experiment with adding a second key on id and doing a self join. The fulltext index will use the one index allowed for the first alias of papers but you can use a second index with a second alias. That would let the id serve as a covering index and avoid the need to scan the table records to count. If you want the title but not the keywords an index on id and title might be of use in the join, since it'll be a covering index able to provide both the id for the join and the title you need.

If your real select is going to use order by title you might also look at ALTER TABLE ... ORDER BY and put the records into title instead of id order.