search - error 1191 - Can't find FULLTEXT
in my forum doesn't work the "search" function
it replay
Can't find FULLTEXT index matching the column
list|Gdn_Database|Query|select *
from (
select match(d.Name, d.Body) against(:Search0) as Relavence
,
d.DiscussionID as PrimaryID
, d.Name as Title
, d.Body as Summary
,
d.Format as Format
, d.CategoryID as CategoryID
,
concat('/discussion/', d.DiscussionID) as Url
, d.DateInserted as DateInserted
, d.InsertUserID as UserID
from GDN_Discussion d
where match(d.Name, d.Body) against (:Search1)
union all
select match(c.Body) against(:Search2) as Relavence
, c.CommentID as PrimaryID
, d.Name as Title
, c.Body as Summary
, c.Format as Format
, d.CategoryID as CategoryID
, concat('/discussion/comment/',
c.CommentID, '/#Comment', c.CommentID) as Url
, c.DateInserted as DateInserted
, c.InsertUserID as UserID
from GDN_Comment c
join GDN_Discussion d on d.DiscussionID = c.DiscussionID
where match(c.Body) against (:Search3)
) s
order by s.DateInserted desc
limit 20_
to every request
i've tried to isolate the problema and in the mysql also a simple
select * from GDN_Comment c where match(c.Body) against ('prova')
give the same error
I have found
From http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html :
For natural-language full-text searches, it is a requirement that the
columns named in the MATCH() function be the same columns included in
some FULLTEXT index in your table.
....
In what way can I fix the problem?
thanks
Best Answer
-
peregrine MVP
I didn't try your first statement, but the second statement worked for me. Maybe this will help.
columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table
show index from GDN_Comment;
(index_type full text shows up for column name body)I ran your last simple test.
I have the word test in the body of several messages and got results with this query.
SELECT * FROM GDN_Comment c WHERE MATCH ( c.Body ) AGAINST ( 'test' ) LIMIT 0 , 30;
if you are trying to do full text on other columns that are currently not full text
ALTER TABLE tablename ADD FULLTEXT name_of_index(example,sample);I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
1
Answers
I didn't try your first statement, but the second statement worked for me. Maybe this will help.
columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table
show index from GDN_Comment;
(index_type full text shows up for column name body)
I ran your last simple test.
I have the word test in the body of several messages and got results with this query.
if you are trying to do full text on other columns that are currently not full text
ALTER TABLE tablename ADD FULLTEXT name_of_index(example,sample);
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
Thank you!
I have created two FULLTEXT index
and it works
.. but why Vanilla doesn't create the indexes by itself?
Who knows? A guess. Perhaps you downloaded an interim version, when the full text index was not implemented properly. Within the past week or so, this forum didn't search properly for a day or so (maybe that was the same problem). I've not run into the problem myself on my install.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.