All geek questions in one place

MySQL Query for choosing the most common substrings separated by spaces in a column?

I have a MySQL database of recipes. One of the NAME columns is varchar (255). Each NAME can be a few words (for example, "Chocolate Chip Cookies" and "Oatmeal Cookies").

I'm looking for a way to efficiently parse recipes into individual words, and then return a list of the most common words. So, given the two recipes above, the request will return:

cookies, 2 chocolate, 1 chip, 1 oatmeal, 1

Is there an effective way to do this? I could query all the results, split each recipe name into words, and then build a data structure to do this outside the database. Alternatively, I could break the lines ahead of time and build a table.

I assume that I really hope this is to use a database to make it faster (e.g. with an index). I do not know if this is possible with a single request.

So, I'm stuck building a table ahead of time? Or can this be done efficiently with a single request?

Thanks!

+4
source share
1 answer

If your table uses the MyISAM storage engine and you create a FULLTEXT index on the NAME column, you can use myisam_ftdump --count option to get the total number of each word. However, unfortunately, this will be outside of SQL.

+1
source

Source: https://habr.com/ru/post/1440343/

More articles:


All Articles