How can I get the most popular words in a table via mysql?

Question

I've got a table with a BLOB collum. What I want to do is get it to be able to pick out words and list them in order.

For example if it contained:

• Bob Smith likes cheese but loves reading
• Charlie likes chocolate milk
• Charl loves manga but also likes cookies

Then I would get

1. likes
2. loves

as a result... is this possible and if so how?

I'd like to be able to do it in just mysql alone, but I can use php as well.

Thanks in advance, kenny

Answer 1

Don't think there is any built in MySQL function to do this so you are probably best using PHP to do the work for you using either explode(' ', $myString) or str_word_count($myString, 1) to create an array containing each word. Then loop through each word in the array and count them.

Answer 2

Sphinx Search (http://sphinxsearch.com/) is a application that does a lot around searching full text in mysql as well as other db's and file formats. In short, it offers search with relavence of mysql tables.

One of it's tools performs word counts, the following is a bash script to get it to output the most popular words in a database:

GET_TOP_X_STOP_WORDS=27
WORD_FREQ_FILE=~/wordfreq.txt
/usr/local/bin/indexer test1 --buildstops $WORD_FREQ_FILE $GET_TOP_X_STOP_WORDS --buildfreqs

Answer 3

I've re-worked my code so I no longer need to do this... it seems impossible with standart setups