Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Fulltext search - eyeballs popping !

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

Page: 1
Go to End
Author Topic: 2074 Views
  • rbarreiros
    Avatar
    Community Member
    21 Posts

    Fulltext search - eyeballs popping ! Link to this post

    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
    Avatar
    Community Member
    21 Posts

    Re: Fulltext search - eyeballs popping ! Link to this post

    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,

    2074 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.