MySQL Forums
Forum List  »  Full-Text Search

Re: FULLTEXT Search performance
Posted by: James Day
Date: December 18, 2004 11:18AM

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.

Options: ReplyQuote

Written By
December 18, 2004 08:18AM
Re: FULLTEXT Search performance
December 18, 2004 11:18AM
December 21, 2004 08:40AM
December 21, 2004 11:27AM
December 22, 2004 02:02AM
December 22, 2004 07:54AM
December 31, 2004 08:09PM
April 15, 2005 08:59AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.