busboy — 2012-06-22T23:06:51-04:00 — #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
r937 — 2012-06-22T23:19:06-04:00 — #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?
busboy — 2012-06-22T23:39:14-04:00 — #3
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,
timeStamp timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (
) 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.
r937 — 2012-06-23T00:07:43-04:00 — #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