mysqlmatchfull-text-searchagainst

MySql FULLTEXT match against a list of keywords from a lookup table

I don't know if this is possible, but it would simplify my calculations to be able to match against each word in a lookup table with a single query. (otherwise, I'll probably pull the tables into memory and program a sequence of queries in python):

SELECT count(*) FROM input_form
WHERE MATCH (title,story) AGAINST (word);

The above will return a count for number of stories that contain 'word', but then is there some way to have it repeat this count for every word in a table containing these words?

SELECT 
word,
count(MATCH (title,story) AGAINST (word))
FROM keywords;

Something like that? Note that title and story are from one table, and word comes from another.

Solution

  • I'm a little rusty, but I think you want to use nested selects: 

    SELECT tbl.word, count(*) FROM (SELECT * FROM input_form WHERE MATCH(title, story) AGAINST (word)) as tbl;

    You may have to add another level of nesting if I recall.