Question about indexes

Databases
1

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

2

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?

3

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!

4

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