Count MySQL Matches in One Column

hankins

I'm building a knowledgebase that searches for articles through a keyword table. When the author creates a new article, the content has all the extraneous noisy words filtered out (a, the, an, or, but, etc) and only the important meaty words are inserted into the keyword search table.

Is there a way that I can determine how many times a keyword is found in a particular column?

Say I have a table named Articles with 2 columns: ID and Keywords.

+-- ID --+---------------- Keywords -----------------+
+ 1 + dogs cats fish dogs animals trees dogs +
+--------+-------------------------------------------+

Is there any way that I can formulate a query to tell me that "dogs" appears 3 times in article ID 1?

This is a query that illustrates what I am trying to do, though I'm fully aware that it doesn't work:

SELECT ID, [number of times a word appears in contents]
AS number_appeared
FROM Articles
WHERE Keywords LIKE "%dogs%"
ORDER BY number_appeared DESC;

Any thoughts? I appreciate it greatly.

[deleted]

First, the LIKE clause will slow you down quite a bit.

I suggest you first run a query that tries to match the word exactly, and only run the LIKE query if the exact match doesn't give enough results.

Second, use the GROUP BY clause in SQL:

SELECT ID, count(*)
FROM table
WHERE keyword LIKE '%dogs%'
GROUP BY ID
ORDER BY 2 DESC;

Note the '2' in the ORDER BY, it order by the second argument in the SELECT section NOT "ORDER BY count(*)"

hankins

Thanks for your response!

I was doing some research on FULLTEXT in the MySQL manual --
(http://www.mysql.com/documentation/mysql/bychapter/manual_Fulltext_Search.html#Fulltext_Search)

Do you think this is a better solution than using LIKE? The manual didn't mention too many negative issues with using MATCH/FULLTEXT -- are there any downsides to using this approach, rather than trying to piece together a query that would pretty much do the same thing, but require 5 times the resources -- which is what it seems like I'm trying to do 🙂?

If FULLTEXT searching is very stable and is good at providing relevant results, my problem may be only as difficult as trying to get the sysadmin to upgrade to version 3.23!

[deleted]

FULLTEXT is bound to be faster, because it uses indexes.

The main drawback of using LIKE is that MySQL cannot use any indexes for it.
That means that every query with a LIKE statement in it will force MySQL to read all the data for that column in all rows, completely.
Et presto, a sequencial scan.

Fulltext does have a few drawbacks that are mentioned in the manual too, like not searching for words smaller than X characters (2 or 3 I believe)

But other than that it's much faster/easier than building a complex query.