Před nějakou dobou jsem potřeboval vytvořit efektivní fulltextové vyhledávání ve velké DB. Sice se informace dají najít, ale zkusím to trošku shrnout.
Představte si, že máte tabulku `tabulka` v MySQL DB.
ID | NAZEV | POPISEK | KATEGORIE
Tato tabulka má statisíce záznamů. Nenapadlo mě nic lepšího než využít fulltextu přímo na úrovni DB. MySQL umožňuje si vytvořit fulltext indexy a podle nich hledat.
CREATE FULLTEXT INDEX ON tabulka(nazev, popisek, kategorie) KEY INDEX fulltext; CREATE FULLTEXT INDEX ON tabulka(nazev) KEY INDEX fulltext_n; CREATE FULLTEXT INDEX ON tabulka(popisek) KEY INDEX fulltext_p; CREATE FULLTEXT INDEX ON tabulka(kategorie) KEY INDEX fulltext_k;
Možná se někomu zdá divné, že jich je třeba vytvořit tolik, ale je to logické. První index pracuje se všemi třemi sloupci a bude nám dávat všechny výsledky. Další 3 indexy budou sloužit k vylazení relevance.
Nyní chceme z DB vybrat všechny výsledky, které obsahují slovo ‘pauza’ a zároveň řadit podle relevance.
SELECT * FROM tabulka WHERE MATCH(nazev, popisek, kategorie) AGAINST('pauza je dlouhá');
Toto by bylo výborné, ale… Nefunguje pro části slov. Klíčové slovo musí mít více než 4 znaky a neberou se v potaz slova, která jsou ve více než 50% záznamů. Navíc se používají stopwords, která se dají nastavit i česká, ale toto mě až tak netrápilo. Nejhorší byla absence hledání podle částí slov. Toto umí boolean mode. Pokud bychom dali MATCH AGAINST do selectu, tak bychom viděli desetinné číslo od 0 do 1, které vyjadřuje relevanci.
SELECT * FROM tabulka WHERE MATCH(nazev, popisek, kategorie) AGAINST('*pauza* *je* *dlouhá*' IN BOOLEAN MODE);
Stopwords se sice stále využívají a stále platí minimální počet znaků (dá se přenastavit v ini souboru mysql) – proto by se „je“ mělo filtrovat už v php, ale pro názornost jsem to tam uvedl. Nicméně už se dají používat asterisky pro části slov. Existuje více takových klíčových znaků. Kompletní výpis zde.
Problémem je, že releavance už je vyjádřena jen jako 1 nebo 0, proto si musíme trochu pomoci.
SELECT * FROM tabulka
WHERE MATCH(nazev, popisek, kategorie) AGAINST('*pauza* *je* *dlouhá*' IN BOOLEAN MODE)
ORDER BY 10 * MATCH(nazev) AGAINST('*pauza* *je* *dlouhá*' IN BOOLEAN MODE)
+ 5 * MATCH(kategorie) AGAINST('*pauza* *je* *dlouhá*' IN BOOLEAN MODE)
+ 1 * MATCH(popisek) AGAINST('*pauza* *je* *dlouhá*' IN BOOLEAN MODE)
;
Nyní vidíme, že pokud je v názvu část těchto slov dostanou 10*, pokud v kategorii tak 5*, pokud v popisku tak 1*. Bylo by odeální v ORDER BY používat MATCH AGAINST bez BOOLEAN MODE, ale potřeboval jsem vyhledávání v částech slov, proto se toto zdá jako nejlepší řešení.
