SilverStripe Forum

• Overview
• User help
• Tutorials
• Documentation
• Forums
• IRC
• Mailing Lists
• Showcase
• Bugs
• How to Contribute
• Donate
• Roadmap
• Search (new!)

• rbarreiros
  
  Community Member
  21 posts
  

  Fulltext search - eyeballs popping !

  10 December 2007 at 7:59pm

  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,

• rbarreiros
  
  Community Member
  21 posts
  

  Re: Fulltext search - eyeballs popping !

  11 December 2007 at 6:39am

  
  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,