Better approach for "LIKE OR LIKE, OR LIKE, OR LIKE, OR LIKE"

Question

In this question he's having the same problem as I am. I need something like:

select * from blablabla 
where product 
like '%rock%' or
like '%paper%' or
like '%scisor%' or
like '%car%' or
like '%pasta%' 

This is ugly and it's not using indexes.. In this case, this is really the only way to do this ( to select multiple words inside a string ), or should I use FULLTEXT?

As I understand, with fulltext, I can select multiple words inside a string.

This question talks about Full Text as well

Answer 1

Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns.

Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use Elasticsearch or something similar).

For smaller use-cases, computed columns are generally a better approach.

Here's a quick demo setup:

use tempdb

CREATE TABLE #fulltextindexesarestupid (Id INT PRIMARY KEY CLUSTERED, StopAbusingFeatures VARCHAR(100))

INSERT #fulltextindexesarestupid (Id)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (@@ROWCOUNT))
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2

UPDATE #fulltextindexesarestupid
SET StopAbusingFeatures = CASE WHEN Id % 15 = 0 THEN 'Bad'
                               WHEN Id % 3 = 0 THEN 'Idea'
                               WHEN Id % 5 = 0 THEN 'Jeans'
                               END


ALTER TABLE #fulltextindexesarestupid 
ADD LessBad AS CONVERT(BIT, CASE WHEN StopAbusingFeatures LIKE '%Bad%' THEN 1
                    WHEN StopAbusingFeatures LIKE '%Idea%' THEN 1
                    ELSE 0 END)

CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #fulltextindexesarestupid (LessBad, Id)

Querying based on even a non-persisted column gives us a plan that 'uses indexes' and everything :)

SELECT COUNT(*)
FROM #fulltextindexesarestupid AS f
WHERE LessBad = 1

Answer 2

sp_BlitzErik's answer hits on a lot of good points, but I don't think that's why you shouldn't use Full Text Search. Full text search isn't there to do what you think it does. It's not there to search multiple fields. It's there to vectorize word content and make use of dictionaries, stubbing, lexers, gazetteers, stop-word elimination, and a slew of other tricks none of which apply. Or, have not yet been shown to apply.

I don't agree with the solution either, though I'm not sure how to do this better in SQL Server. Let's recreate his data for PostgreSQL -- it's a lot cleaner to create in in PostgreSQL too.

CREATE TABLE fulltextindexesarestupid
AS
  SELECT
    id,
    CASE WHEN Id % 15 = 0 THEN 'Bad'
      WHEN Id % 3 = 0 THEN 'Idea'
      WHEN Id % 5 = 0 THEN 'Jeans'
    END AS StopAbusingFeatures
  FROM generate_series(1,1000000) AS id;

Now what you want is an enum type,

CREATE TYPE foo AS ENUM ('Bad', 'Idea', 'Jeans');

ALTER TABLE fulltextindexesarestupid
  ALTER StopAbusingFeatures
  SET DATA TYPE foo
  USING StopAbusingFeatures::foo;

Now you've collapsed the strings to integer representations. But even better you can query them like before.

SELECT *
FROM fulltextindexesarestupid
WHERE StopAbusingFeatures = 'Bad';

This has the effect.

1. conceals the fact that you're categories are an enumerated type. That complexity is encapsulated in the type and hidden from the user.
2. it also places the maintenance on those categories on the type.
3. it's standardized.
4. it doesn't grow the row size.

Without these benefits, you're essentially just trying to optimize out string comparison. But alas, I'm not even sure how sp_BlitzErik gets to the answer given the code in the suggestion,

like '%rock%' or
like '%paper%' or
like '%scisor%' or
like '%car%' or
like '%pasta%'

You can collapse the tokens down to integers using an enum, or the hand-rolling method suggested by sp_BlitzErik but if you can do the collapsing why are you doing the unanchored-like too? Ie, If you know '%pasta%' is the token 'pasta' why do you have the % on both sides of it. Without '%' this an equality check and it should pretty fast even as text.