Question about indexes

[busboy]

I added a new column called 'title', which is basically a duplicate of another column called summary. I then edited my index to include this new column, but now the following query errors out. What did I do wrong?


Query: SELECT tID, date_format(dateAdded, '%m-%d-%Y') as dateAdded, viewed, title, MATCH (title,testimony,keywords) AGAINST ('lemon') AS score FROM testimonies WHERE MATCH (title,testimony,keywords) AGAINST ('lemon') and approved = 'Yes' order by score desc limit 50
Error: mysql_errno() Can't find FULLTEXT index matching the column list

[r937]

could you do a SHOW CREATE TABLE please?

meanwhile, a comment or two about your query

you don't have to force the ORDER BY for score, because that's the default

also, if you expect to find the search word in one of three columns, you should include all three columns in the SELECT clause

also, did you fix your Unknown column 'memberType ' problem in the other thread?

[busboy]

CREATE TABLE `testimonies` (
`tID` int(8) NOT NULL AUTO_INCREMENT,
`date` datetime DEFAULT NULL,
`dateAdded` datetime DEFAULT NULL,
`author` int(4) NOT NULL DEFAULT '0',
`uID` int(5) NOT NULL DEFAULT '0',
`approved` varchar(35) DEFAULT NULL,
`viewed` int(4) DEFAULT '0',
`questions` int(4) DEFAULT '0',
`keywords` varchar(100) DEFAULT NULL,
`summary` varchar(100) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`testimony` mediumtext,
`timeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`tID`),
FULLTEXT KEY `summary` (`summary`),
FULLTEXT KEY `keywords` (`keywords`,`summary`,`testimony`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=7463 DEFAULT CHARSET=latin1

The purpose of my code change is to being the process of switching over to new column names that I will start using from now on with my new website, while the old website continues to reference the older column names.

And unfortunately I haven't solved the other issue with 'memberType '

Can you send me a private note? I think I would like to pay you some money to login to phpMyAdmin one of these days to help tune my whole database.

Thanks!

[r937]

FULLTEXT KEY `summary` (`summary`),
FULLTEXT KEY `keywords` (`keywords`,`summary`,`testimony`),
FULLTEXT KEY `title` (`title`)

there's the reason you were getting that error message

none of your fulltext indexes is built on the three columns you were trying to search -- title,testimony,keywords