Hello guys,

I have a problem (am sure it's mysql related tbh) i've been all night trying to find what's wrong and i can't, must be because am really sleepy...

Trying to do a fulltext search,

return ($news) ? DataObject::get('News', "MATCH (Title,MenuTitle) AGAINST ('$this->MetaKeywords')") : false;

If i try a match with Title only, works, with Title and MenuTitle doesn't complaining the column doesn't have fulltext search on, checked the table indexes:

| SiteTree_Live |          1 | SearchFields      |            1 | Title           | NULL      |          18 |     NULL | NULL   | YES  | FULLTEXT   |         |
| SiteTree_Live |          1 | SearchFields      |            2 | MenuTitle       | NULL      |          18 |     NULL | NULL   | YES  | FULLTEXT   |         |
| SiteTree_Live |          1 | SearchFields      |            3 | Content         | NULL      |          18 |     NULL | NULL   | YES  | FULLTEXT   |         |
| SiteTree_Live |          1 | SearchFields      |            4 | MetaTitle       | NULL      |          18 |     NULL | NULL   | YES  | FULLTEXT   |         |
| SiteTree_Live |          1 | SearchFields      |            5 | MetaDescription | NULL      |          18 |     NULL | NULL   | YES  | FULLTEXT   |         |
| SiteTree_Live |          1 | SearchFields      |            6 | MetaKeywords    | NULL      |          18 |     NULL | NULL   | YES  | FULLTEXT   |         |
| SiteTree_Live |          1 | TitleSearchFields |            1 | Title           | NULL      |          18 |     NULL | NULL   | YES  | FULLTEXT   |         

2 Indexes, 1 with Title only and another with several oter fields including Title and MenuTitle.
Everytime i try with any field which is not Title i get the same error:

SELECT `SiteTree_Live`.*, `SiteTree_Live`.ID, if(`SiteTree_Live`.ClassName,`SiteTree_Live`.ClassName,'SiteTree') AS RecordClassName FROM `SiteTree_Live` WHERE (MATCH (Title,MenuTitle) AGAINST ('study')) AND (`SiteTree_Live`.ClassName IN ('News')) ORDER BY Sort;
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

Any of u guys has any idea what am i doing wrong or what's wrong ?

Best regards,

Hello,

After a good night sleep, i found the answer :) so here it stays for everyone that might run into the same problem.

Mysql only allows fulltext search for the indexed columns, no more no less, there are 2 indexes:

TitleSearchFields which has column Title only
SearchFields which has columns Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords

so, the only searches u can make are with Title only (1st index) or all of the columns on SearchFields index, you need to use them all in the query, no more, no less, or mysql complaints that couldn't find the index with those fulltext indexed columns.

Hope this might be usefull for someone,

Best regards,