How can I select MySQL full-text index entries for full-featured functions?

Question

Is there a way to select records from a full-text index in MySQL?

Answer 1

No, not what I know. This will be a great opportunity.

I built an autocomplete search interface on top of MySQL. I run a daily job that scans all the columns in all the tables I want to look up, extract words with regular expressions, and then store the words in a separate table. I also have a many-to-many table with one column for storing the identifier of the object and one column for storing the identifier of the word, to record the fact that “the word is part of the text belonging to the object”.

Autocomplete works by taking the words entered into the field and then generates a query that looks like this:

SELECT     obj.title
FROM       obj_word
INNER JOIN obj
ON         obj_word.obj_id = obj.id
INNER JOIN word
ON         obj_word.word_id = word.id
WHERE      word.word IN ('word1', 'word2', 'word3') -- generated dynamically, word1 etc     are typed by the user
GROUP BY   obj.id
HAVING COUNT(DISTINCT word.id) = 3 -- the 3 is generated, because user typed 3 words.

This works pretty well for me, but I don't have huge amounts of data to work with.

(the actual implementation is a bit interesting, because the last word matches LIKE to allow partial matches)

EDIT:

, myisam_ft_dump . :

myisam_ftdump -d film_text 1  > D:\tmp\out.txt

-d ( ), film_text - MyISAM , 1 , , .

, , , , . , , . , .

, , , .