Count the frequency of each word

Question

I've been trolling the internet and realize that MySQL is not the best way to get at this but I'm asking anyway. What query, function or stored procedure has anyone seen or used that will get the frequency of a word across a text column.

    ID|comment
    ----------------------
 Ex. 1|I love this burger
     2|I hate this burger

     word   |  count
     -------|-------
     burger |  2
     I      |  2
     this   |  2
     love   |  1
     hate   |  1

@clhereistian not passing in any words and MySQL is my only option (right now) — Atwp67, Mar 14 '14 at 16:42
wow. you mean you want to parse all the words out of the column and then count how many times each one occurs? — clhereistian, Mar 14 '14 at 16:44
This is def better suited for a programming language like php. — clhereistian, Mar 14 '14 at 16:45
The tricky part is getting your collection of words. Mysql does not have a string split function. If you search SO, you can find a stored proc that will split the text from your columns and output them as a result set. Then you could use something like my answer on them. — clhereistian, Mar 14 '14 at 16:47
@clhereistian I've seen, but sql is what I've got right now. — Atwp67, Mar 14 '14 at 16:48

score 2 · Accepted Answer · answered Mar 14 '14 at 16:48

This solution seems to do the job (stolen almost verbatim from this page). It requires an auxiliary table, filled with sequential numbers from 1 to at least the expected number of distinct words. This is quite important to check that the auxiliary table is large enough, or results will be wrong (showing no error).

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(maintable.comment, ' ', auxiliary.id), ' ', -1) AS word,
    COUNT(*) AS frequency
FROM maintable 
JOIN auxiliary ON
    LENGTH(comment)>0 AND SUBSTRING_INDEX(SUBSTRING_INDEX(comment, ' ', auxiliary.id), ' ', -1)
    <> SUBSTRING_INDEX(SUBSTRING_INDEX(comment, ' ', auxiliary.id-1), ' ', -1)
GROUP BY word
HAVING word <> ' '
ORDER BY frequency DESC;

SQL Fiddle

This approach is as inefficient as one can be, because it cannot use any index.

As an alterative, I would use a statistics table that I would keep up-to-date with triggers. Perhaps initialise the stats table with the above.

clhereistian · Answer 2 · 2014-03-14T16:34:02.703

-1

Something like this should work. Just make sure you don't pass in a 0 length string.

SET @searchString = 'burger';

SELECT 
    ID, 
    LENGTH(comment) - LENGTH(REPLACE(comment, @searchString, '')) / LENGTH(@searchString) AS count
FROM MyTable;

edited Mar 14 '14 at 16:34

answered Mar 14 '14 at 16:26

clhereistian

1,261
1
11
19

Count the frequency of each word

2 Answers2

Linked