mysql - 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

Answer 1

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.