TalkPHP - MySQL5 Fulltext

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - MySQL5 Fulltext (http://www.talkphp.com/mysql-databases/2215-mysql5-fulltext.html)

WinSrev

02-09-2008 03:53 PM

MySQL5 Fulltext

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? :-D

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.

All times are GMT. The time now is 07:00 AM.