How to get word frequency per line using mysql fulltext

Question

I have a MyISAM table containing more than 2 million records that has a FULLTEXT index across multiple columns.

Given the search query, I would like to know how many times this happens in the indexed fields of each record.

For example, when searching for 'test' in the following table (which has a FULLTEXT index for the FREETEXT and Third_Col ):

  + ---- + -------------------------------------------- + --------------------------- +
 |  ID |  FREETEXT |  Third_Col |
 + ---- + -------------------------------------------- + --------------------------- +
 |  1 |  This is first test string in test example.  |  This is first test Values ​​|
 |  2 |  This is second test.  |  This is sec col |
 + ---- + -------------------------------------------- + --------------------------- +

I expect results like:

  + ---- + ------- +
 |  ID |  count |
 + ---- + ------- +
 |  1 |  3 |
 |  2 |  1 |
 + ---- + ------- +

I know that in the FULLTEXT index FULLTEXT MySQL uses dtf (the number of times the term appears in the document); how can i get this?

Answer 1

Create a user-defined function like this

 DELIMITER $$ CREATE FUNCTION `getCount`(myStr VARCHAR(1000), myword VARCHAR(100)) RETURNS INT BEGIN DECLARE cnt INT DEFAULT 0; DECLARE result INT DEFAULT 1; WHILE (result > 0) DO SET result = INSTR(myStr, myword); IF(result > 0) THEN SET cnt = cnt + 1; SET myStr = SUBSTRING(myStr, result + LENGTH(myword)); END IF; END WHILE; RETURN cnt; END$$ DELIMITER ; 

Then you can use this in your request as follows

 select id, getCount(concat(FREETEXT, Third_col), 'test') from yourtable 

Hope this helps