[Resolved] Query trouble (MATCH AGAINST)

laeelin

the query is: SELECT * FROM products WHERE MATCH (short,meddes) AGAINST('leather desk set')

When i run it i get the error:

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

But, If i run it as:
the query is: SELECT * FROM products WHERE MATCH (short) AGAINST('leather desk set')

or:

the query is: SELECT * FROM products WHERE MATCH (meddes) AGAINST('leather desk set')

It works.

The second problem is that when i made this database I wasnt thinking and gave 2 bad Field names... one is short and the other is long .. i'll be fixing that, but i dont think that has anything to do with this problem..

Thanks

David

Roger_Ramjet

It means what it says: that you do not have a FULLTEXT INDEX that includes the named columns. You have to specifically create the fulltext index in order to use it.
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

"The MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. "

laeelin

It means what it says: that you do not have a FULLTEXT INDEX that includes the named columns.

But it works with each one separately.. It’s only when I try to run both at the same time that I get the error.

Roger_Ramjet

Do you have a multi-column full text index that include the 2 columns? You must specifically create the index on the 2 columns, not 2 indexes each on one column. If you just have fulltext indexes on the individual columns then you will be able to run the match on each one seperately, but not on them both together.

laeelin

Do you have a multi-column full text index that include the 2 columns? You must specifically create the index on the 2 columns, not 2 indexes each on one column. If you just have fulltext indexes on the individual columns then you will be able to run the match on each one seperately, but not on them both together.

aah!!!

Thanks =)

I didnt knwo that =)

laeelin

I'm getting this error createing the index..

ALTER TABLE products ADD INDEX prodtext ( shorttext , longtext , meddes )

I get this error:

#1170 - BLOB column 'longtext' used in key specification without a key length

============

When I try to enter a length with:

ALTER TABLE products ADD INDEX ( shorttext ( 25 ) , longtext ( 3000 ) , meddes ( 1500 ) )

I get this error:

#1089 - Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the table handler doesn't support unique sub keys

Any suggestions?
Thanks
David

Roger_Ramjet

Now you've got me. I have only ever used phpMySqlAdmin on mysql databases so I'm nowhere with the sql dialect that it uses for Alter Table. Sorry.

laeelin

Well, I found out what the problem was..

Looks like the database was corrupted.. I exported the data, deleted the table, and then recreated the table and imported the data.

Then I could create the full text search.

Thanks
David