Lepsze podejście do „PODOBNE LUB PODOBNE, LUB PODOBNE, LUB PODOBNE, LUB PODOBNE”

W tym pytaniu ma ten sam problem co ja. Potrzebuję czegoś takiego:

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

Jest to brzydkie i nie używa indeksów. W takim przypadku jest to naprawdę jedyny sposób, aby to zrobić (aby wybrać wiele słów w ciągu), czy też powinienem użyć FULLTEXT?

Jak rozumiem, przy pomocy pełnego tekstu mogę wybrać wiele słów w ciągu.

To pytanie dotyczy również pełnego tekstu

Indeksy pełnotekstowe na ogół nie są magiczną kulą i wymagają dodatkowej konserwacji, miejsca na dysku i dość ingerujących zmian we wzorcach zapytań.

O ile naprawdę nie potrzebujesz indeksowania dużych dokumentów (uważaj treści wiadomości e-mail, pliki PDF, dokumenty Word itp.), Są one przesadne (i jeśli jesteśmy szczerzy, całkowicie usunę ten proces z SQL Server użyj Elasticsearch lub czegoś podobnego).

W przypadku mniejszych przypadków użycia kolumny obliczane są ogólnie lepszym podejściem.

Oto szybka konfiguracja wersji demo:

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)

Kwerenda oparta nawet na nietrwałej kolumnie daje nam plan, który „używa indeksów” i wszystkiego :)

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

Odpowiedź sp_BlitzErik zawiera wiele dobrych punktów, ale nie sądzę, że dlatego nie powinieneś używać wyszukiwania pełnotekstowego . Wyszukiwanie pełnotekstowe nie służy do robienia tego, co myślisz. Nie ma tam wyszukiwania wielu pól. Ma na celu wektoryzację treści słów i korzystanie ze słowników, stubowania, leksykonów, gazet, eliminacji słów zatrzymanych i mnóstwa innych sztuczek, z których żadna nie ma zastosowania. Lub, jeszcze nie wykazano ich zastosowania.

Nie zgadzam się również z tym rozwiązaniem, chociaż nie jestem pewien, jak to zrobić lepiej w SQL Server. Utwórzmy ponownie jego dane dla PostgreSQL - tworzenie PostgreSQL jest o wiele czystsze.

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;

Teraz chcesz tego typu enum,

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

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

Teraz zwinąłeś ciągi do reprezentacji liczb całkowitych. Ale jeszcze lepiej możesz zapytać o nie tak jak wcześniej.

SELECT *
FROM fulltextindexesarestupid
WHERE StopAbusingFeatures = 'Bad';

To ma efekt.

1. ukrywa fakt, że kategorie są typem wyliczonym. Ta złożoność jest zawarta w typie i ukryta przed użytkownikiem.
2. umieszcza także konserwację tych kategorii na typie.
3. jest ustandaryzowany.
4. nie zwiększa rozmiaru wiersza.

Bez tych korzyści zasadniczo próbujesz zoptymalizować porównanie ciągów znaków. Ale niestety nie jestem nawet pewien, jak sp_BlitzErik dostaje odpowiedź na podany kod w sugestii,

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

Możesz zwinąć tokeny do liczb całkowitych za pomocą wyliczenia lub metody zwijania rąk sugerowanej przez sp_BlitzErik, ale jeśli możesz zrobić zwijanie, dlaczego robisz też niezakotwiczone? To znaczy, jeśli wiesz, że „% makaron%” jest tokenem „makaron”, dlaczego masz %po obu stronach. Bez „%” jest to kontrola równości i powinna być dość szybka, nawet jako tekst.