MySQL – Filtering SELECT Query Results to Avoid Certain Words

mysql-5.5selectstringsubstringvarchar

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the content.

For example

porn, pornography, Adult, Boobs, Booty, Tits

pseudo query may be like

SELECT * FROM videos WHERE title NOT LIKE (porn, pornography, Adult, Boobs, Booty, Tits)

I checked NOT LIKE but I have to do it like

SELECT * FROM videos WHERE title NOT LIKE ‘%porn%’ AND title NOT LIKE ‘%pornography%’ AND title NOT LIKE ‘%Adult%’ AND title NOT LIKE ‘%Booty%’ AND title NOT LIKE ‘%Boobs%’ AND title NOT LIKE ‘%Tits%’

which is very inconvenient and inefficient. So is there any other better way to do this ?

Best Answer

I've written this in SQL Server so you may have to translate to MySQL but this should work for you.

CREATE TABLE #ExcludeList (ExcludeVal varchar(50))

CREATE TABLE #VideoList (VideoName varchar(500))

INSERT INTO #ExcludeList VALUES ('Prob1'),('Prob2'),('DontUse1')

INSERT INTO #VideoList VALUES ('This is a prob1'),
            ('This is clean'),('This is also clean'),
            ('DontUse1 this one is bad')

SELECT *
FROM #VideoList VL
WHERE NOT EXISTS (SELECT 1 FROM #ExcludeList EL
                    WHERE VL.VideoName LIKE '%' + EL.ExcludeVal + '%')

Related Solutions

Mysql – Order of where clause evaluation

See this answer. No, the order you put the conditions in the WHERE clause doesn't matter: In MySQL, does the order of the columns in a WHERE clause affect query performance?

If id is not nullable, you can replace the count(id) with count(*) and try adding an index on (_campaign, session, timeCreated).

ALTER TABLE mDelivered
  ADD INDEX campaign_session_timeCreated_IX        -- choose a name for the index
    (_campaign, session, timeCreated) ;

And you don't need the GROUP BY since you are already restricting the rows to fixed _campaign and session values. Try this:

SELECT count(*) cnt
FROM mDelivered
WHERE _campaign = 2
  AND `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f' 
  AND timeCreated > NOW() - INTERVAL 1 HOUR ;

Mysql – Rich text: VARCHAR, TEXT or BLOB in MySQL

It would depend on the size of the data you want to save in that field. You should know that there is a limit for VARCHAR, and that limit is in bytes, so number of characters that can be stored will depend on the character set you use. For example, if you use UTF 8, you will be able to store less amount of data.

To sum it up, there is no "best" solution, but if your data fits in VARCHAR, then use it :). If not, you will have to use TEXT or BLOB, or you will have to zip the data before saving it (Check compress)

Best Answer

Related Solutions

Mysql – Order of where clause evaluation

Mysql – Rich text: VARCHAR, TEXT or BLOB in MySQL

Related Question