Posted by Tom Cunningham on February 5 2004 5:42am

Tom's fulltext tips: To get MySQL searching well for me I did:

1. Have a normalized versions of the important columns: where you've stripped punctuation and converted numerals to words ('1' to 'one'). Likewise normalise the search string.

2. Have a combined fulltext index on your searchable columns to use in your 'WHERE' clause, but then have separate fulltext indexes on each column to use in the 'ORDER BY' clause, so they can have different weights.

3. For the scoring algorithm, include the independent importance of that record, and include a match of the inclusive index against stemmed versions of the search words (as: "wedding" => "wed", "weddings").

4. If there's exactly one result, go straight to that record.

5. If you get no results, try matching against the start of the most important column (WHERE column LIKE 'term%'), and put a 5-character index on that column. This helps if someone is searching on a very short word or a stopword.

6. Reduce minimum word length to 3, and make a new stopwords list just using "a an and the is in which we you to on this by of with". Use "REPAIR TABLE xxx QUICK" to rebuild the index and make a note of the index-file (xxx.MYI) size before and after you make changes. Then use ft_dump to tune.