I have a table (INNODB db) with thousands of records and some columns are fulltext indexed. I use fulltext in boolean mode and it works very well, except for the row with id 1. It always show me that relevance is 0 even if searched word/s exists inside indexed columns, and on other rows that word is regularly found. I've already checked up stop words but that's not the problem. It's like fulltext search is blind on row with id 1. Any suggestions?
An example of behaviour:
SELECT *, MATCH(full_name, short_name, long_description, summary, model) AGAINST("snom" IN BOOLEAN MODE) as relevance FROM product_sheets
Result:
| id | full_name | short_name | model | long_description | summary | relevance |
|---|---|---|---|---|---|---|
| 1 | Snom phone black 12 lines | Snom phone | D385 | Snom phone .. | Snom, phone | 0 |
| 2 | Snom phone blue 12 lines | Snom phone | D385 | Snom phone .. | Snom, phone | 38.311.. |
| .. | .. | .. | .. | .. | .. | .. |
Full text index is applied on columns shown in snippet.
(This may or may not fix the index. Please try it.)
In the page on OPTIMIZE TABLE:
After doing substantial insert, update, or delete operations on columns that are part of a FULLTEXT index in an InnoDB table. Set the configuration option innodb_optimize_fulltext_only=1 first. To keep the index maintenance period to a reasonable time, set the innodb_ft_num_word_optimize option to specify how many words to update in the search index, and run a sequence of OPTIMIZE TABLE statements until the search index is fully updated.
(Please report back on whether it fixed your problem.)