I'm getting the error:
"Can't find FULLTEXT index matching the column list"
I have found that the column list in my query must EXACTLY match the column list in my FULL TEXT index.
So if I want to query like this:
WHERE MATCH (FileText,OrigAuthor,LastModAuthor,Description,Custom5Value)
AGAINST ('one for the money')
. . .I must create my fulltext index like this:
CREATE TABLE FileMetadata (
FileMetadataID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
FileID int(11) NULL,
FolderID int(11) NULL,
ProjectID int(11) NULL,
FileText text NULL,
FileTextPage int(11) NULL,
OrigAuthor varchar(50) NULL, #mandatory metadata field
LastModAuthor varchar(50) NULL, #mandatory metadata field
Description varchar(100) NULL, #mandatory metadata field
Custom1Value varchar(50) NULL,
Custom2Value varchar(50) NULL,
Custom3Value varchar(50) NULL,
Custom4Value varchar(50) NULL,
Custom5Value varchar(50) NULL,
FULLTEXT KEY idxFileMetaData (FileText,OrigAuthor,LastModAuthor,Description,Custom5Value)
. . .but this is not practical for me, because any combination of those fields can be searchable by a user. My UI allows users to select which fields to search against. If their selection happens to not correspond with the exact fields as specified in the fulltext key, I get the error mentioned above. Even if I include EVERY text-like column in the table, in the fulltext index, if the user wants to search only against a subset of those columns, the error will result.
It's hard for me to believe that MySQL 4.1 could possibly be this inflexible. . .? I must be doing something wrong. How can I include all text-like columns in my fulltext index, but still allow users to search against any desired subset of those columns?
Or is there some trick for an alternative? I guess if i had to query against all columns every time, is there a way for me to exclude records that matched against columns that the user did not want to include in the search?