php & mysql: use a table for a filter list for another table

Question

I have two mysql tables. One is a bad words list, the other is the table to compare against the bad words list. Essentially I want to filter out and return a list of rows with domains that do not have ANY occurrence of a word in the bad words table. A few sample tables:

bad words list

+----------+------------------+
| id       | words            |
+----------+------------------+
| 1        | porn             |
| 2        | sex              |
+----------+------------------+

table of domains to compare

+----------+------------------+
| id       | domain           |
+----------+------------------+
| 56       | google.com       |
| 57       | sex.com          |
+----------+------------------+

I want to return results such as

+----------+------------------+
| id       | domain           |
+----------+------------------+
| 56       | google.com       |
+----------+------------------+

A thing to note is that these tables have nothing in common, so I'm not even sure this is the best method. I was using a comparison function in PHP but that seemed to be way too slow over hundreds of thousands of rows to search.

Answer 1

It is possible to get from mysql. like this:

SELECT
    d.*
FROM 
    domains d 
LEFT JOIN
    words w ON(d.domain LIKE CONCAT('%',w.word,'%') ) 
GROUP BY
    d.domain
HAVING
    COUNT(w.id) < 1

but it is not optimal and will get slower and slower with more records in both tables.

Answer 2

Data like this typically needs to be pre-calculated at insertion time rather than at fetch time. You should add a column to Domains something like "bad_words boolean default null".

null would mean "don't know" which in some context could be interpretted as "unsafe to show". false means "no bad words" and true means "contains bad words".

Everytime the list of bad words is updated all columns are reset to null and some background work will start to process them again. Probably in another language than sql.