Cross-checking one MySQL table of text content against another table of keywords

Question

Let's say I have two tables:

Table 1 has the columns NOTE_ID (a unique key) and NOTE_BODY (a big text blurb).

Table 2 has the columns KEYWORD_ID (a unique key) and KEYWORD (a keyword).

I want to get a result set that tells me which keywords each NOTE_BODY contains, without nesting a bunch of loops. So ideally I would get a row for each NOTE_BODY-KEYWORD match.

What's the right way to go about this? I'm unsure if a JOIN+LIKE'%%' does the trick, or if I should be using full-text indexing. Any help much appreciated...

Answer 1

A full text indexing solution is the right way to do this, if you plan to have many rows. You could use MySQL's native solution if you are using the MyISAM storage engine, but you could also consider the popular third-party search engines Sphinx and Apache Lucene.

On the other hand, a simple INNER JOIN would have done the trick:

SELECT  t1.note_id, t1.note_body, t2.keyword
FROM    table_1 t1
JOIN    table_2 t2 ON (t1.note_body LIKE CONCAT('%', t2.keyword, '%'));

Test case:

CREATE TABLE table_1 (note_id int, note_body varchar(100));
CREATE TABLE table_2 (keyword_id int, keyword varchar(50));

INSERT INTO table_1 VALUES (1, 'Hello Stack Overflow');
INSERT INTO table_1 VALUES (2, 'Hello World');
INSERT INTO table_1 VALUES (3, 'Hello, my name is Daniel');
INSERT INTO table_1 VALUES (4, 'Goodbye');

INSERT INTO table_2 VALUES (1, 'Hello');
INSERT INTO table_2 VALUES (2, 'name');

Result:

+---------+--------------------------+---------+
| note_id | note_body                | keyword |
+---------+--------------------------+---------+
|       1 | Hello Stack Overflow     | Hello   |
|       2 | Hello World              | Hello   |
|       3 | Hello, my name is Daniel | Hello   |
|       3 | Hello, my name is Daniel | name    |
+---------+--------------------------+---------+
4 rows in set (0.00 sec)