Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive /

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

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

Fulltext search - eyeballs popping !


Reply


2 Posts   2140 Views

Avatar
rbarreiros

Community Member, 21 Posts

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,

Avatar
rbarreiros

Community Member, 21 Posts

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,