MySQL5 Fulltext

WinSrev · 02-09-2008, 03:53 PM

Hey,

Been trying to search through my database using fulltext, i used the query:

Code:

SELECT *
FROM `table1`
LEFT JOIN `table2` ON `table1`.`col1` = `table2`.`col2`
WHERE MATCH (
major1, major2
)
AGAINST (
'theory'
)
LIMIT 0 , 30

But the problem it gives an error:

Quote:

#1191 - Can't find FULLTEXT index matching the column list

although running each column seperatly works. Any ideas?

Thanks!

xenon · 02-09-2008, 05:19 PM

You probably ment:

Code:

SELECT *
FROM `table1` tbl1
LEFT JOIN `table2` tbl2 ON `table1`.`col1` = `table2`.`col2`
WHERE MATCH (
tbl2.major1, tbl2.major2
)
AGAINST (
'theory'
)
LIMIT 0 , 30

WinSrev · 02-09-2008, 05:36 PM

Can't be that, it produces the same error.

Alan @ CIT · 02-09-2008, 06:38 PM

Try creating a new index - MySQL AB :: MySQL 5.0 Reference Manual :: 11.1.4 CREATE INDEX Syntax - on the tables and your searchable columns to see if that helps.

Alan

WinSrev · 02-09-2008, 06:42 PM

Well, i know for a fact that they have indexes because as i said they work seperatly but wont work together for some reason.

Alan @ CIT · 02-09-2008, 06:48 PM

Indeed. The only reason I can think of is a corrupt index somewhere (even though they work seperately), hence the index rebuild.

Hell, it's worth a try anyway?

Alan

Alan @ CIT · 02-09-2008, 06:53 PM

Also, when you created your table did you create it with FULLTEXT(major1, major2)? or seperately?

Edit: Having done some testing, I reckon this is where the problem lies. To use MATCH(major1, major2), your create table statement would have needed "...FULLTEXT(major1, major2)...", I'm guessing that it had "...FULLTEXT(major1), FULLTEXT(major2)..." which is why it works on single column searches.

Alan

WinSrev · 02-09-2008, 08:45 PM

Yeah, i see what you mean, but, what if i want to do it over 2 tables? think that would be possible?

Alan @ CIT · 02-09-2008, 08:50 PM

MySQL AB :: Re: Full Text search on multiple tables? - That post may help - he seems to be doing a full text search across two tables.

Alan

WinSrev · 02-10-2008, 12:40 AM

Thanks for all your help Alan, problem solved.