I notice that when I add EXPLAIN to my search, the key_len of my fulltext index is 0. Does that mean my fulltext index is empty, explaining why my searches never return any results?
Either way, I'd be grateful for any suggestions on how to get fulltext search to work!
Config: MySQL 3.23.55 running on my webhost's Linux box phpMyAdmin 2.1.0
Long description:
My test database has two tables and 16 rows. The table named "entry" starts out looking like this:
Field Type Attributes Null Default Extra ------------------------------------------------------------------- itemid mediumint(8) UNSIGNED No 0 permid mediumint(8) UNSIGNED No 0 eventtime datetime No 0000-00-00 00:00:00 subject varchar(255) Yes event text Yes current_music varchar(255) Yes current_mood varchar(255) Yes
Indexes Keyname Unique Field -------------------------- PRIMARY Yes itemid
To allow fulltext search, I add a fulltext index via phpMyAdmin's "Run SQL query" textarea:
ALTER TABLE entry ADD FULLTEXT subject_index (subject);
I log out of and back into phpMyAdmin. Now there is a new row in the list of indexes:
Keyname Unique Field ------------------------------ PRIMARY Yes itemid subject_index No subject
I do a fulltext search for a term I know is in the "subject" data:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
But I get back an error/empty results:
Error
SQL-query:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
MySQL said:
I run the query again, using EXPLAIN:
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact');
The query is echoed (with an additional LIMIT apparently added by phpMyAdmin):
EXPLAIN SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('contact'); LIMIT 0, 30
And the EXPLAIN output is:
table type possible_keys key key_len ref rows Extra -------------------------------------------------------------------------- entry fulltext subject_index subject_index 0 1 where used
_________________________________________________________________
Watch high-quality video with fast playback at MSN Video. Free! http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
