9

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

  • 3
    What's the data type of the product column? How many characters on average? – Joe Obbish Aug 4 '17 at 4:16
17

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

NUTS

  • Erik I will accept this as an answer because it answer my question, But just to clear my mind. Can Fulltext indexes search INSIDE the pdf/word file that is on the table as a binary? ( i'm not doing this. i'm just curious ). Because my question was to search inside a really big string , with let's say, 2000 words ). But reading your answer I could think better about it. – Racer SQL Aug 3 '17 at 15:31
  • @RafaelPiccinelli yes, if you Google for "can sql server full text search look inside files," you'll get lots of information about that. – Brent Ozar Aug 3 '17 at 15:35
  • 1
    @sp_BlitzErik Where did the jeans go in the example? – hot2use Aug 3 '17 at 16:08
-3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.