CODEWITHSUNDEEP
mysqlfull-text-search
Sapan
Sapan

Reputation: 41

How to get frequency of a word in a row using mysql fulltext

I have a MyISAM table comprising over 2 million records, on which there is a FULLTEXT index over multiple columns.

Given a search term, I would like to know how many times it occurs within the indexed fields of each record.

For example, when searching for 'test' within the following table (in which there is a FULLTEXT index over both the FREETEXT and Third_Col columns):

+----+--------------------------------------------+---------------------------+
| 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 MySQL uses dtf (the number of times the term appears in the document); how can one obtain this?

Upvotes: 4

Views: 1579

Answers (1)

Akhil
Akhil

Reputation: 2602

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 query as follows 

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

Hope it helps

Upvotes: 2

Related Questions