mysql FULLTEXT works on one index but not another

[ocelot]

I have a table called blog_posts and I clicked the FULLTEXT link in phpMyAdmin for the rows "keywords" and "body". Both times it said "fulltext index successfully added" or whatever.

but when I say

SELECT * FROM blog_posts WHERE MATCH(keywords,body) AGAINST('real')

it says it can't find an index for the requested rows.

but when I say

SELECT * FROM blog_posts WHERE MATCH(body) AGAINST('real')

it returns the right rows with no errors.

I tried dropping and readding the fulltext index for "keywords" but it didn't help.
What's going on?

[ergophobe]

ocelot,

Sorry but I'm not that familiar with phpmyadmin. I know you can enter straight SQL in it though, so lets just work with that (or open the default client if you know how to do that).

1. get into your DB
- either navigate to it in phpMyAdmin
- or from the mysql client command prompt type: USE `db_name`;

2. type: SHOW KEYS FROM `table_name`;

If the col is full text indexed, it should say so in the comments column of the output.

Tom

[ocelot]

yeah it says it's full indexed. I did your query and it gave me a table which said 'keywords' has 'index_type' 'FULLTEXT'

[ergophobe]

Hmmm. Try enclosing the column names (they're actually columns, not rows) in `backticks` and double checking the spelling. Other than that, I'm at a loss.

Hope you figure it out.

Tom