Fulltextové vyhledávání v MySQL

Školení, která pořádám

Pro prohledávání textů v MySQL se dá často použít jednoduchý operátor LIKE, pro složitější dotazy se ale může hodit fulltextové vyhledávání, kterým MySQL disponuje. To má dvě hlavní varianty: vyhledávání v přirozeném jazyce, které výsledkům zároveň přiřazuje míru relevance, a boolean vyhledávání, které dovoluje používat operátory zpřesňující výsledky. Obě varianty mají několik omezení: ignorují se běžná anglická slova (lze změnit proměnnou ft_stopword_file), minimální délka indexovaného slova je 4 znaky (lze změnit proměnnou ft_min_word_len) a při vyhledávání v přirozeném jazyce se navíc poněkud alibisticky ignorují slova obsažená alespoň v polovině dokumentů (lze změnit pouze překompilováním MySQL). Dobrá zpráva je, že od MySQL 4.1.1 lze fulltextové vyhledávání používat i s kódováním UTF-8.

Hlavně kvůli 50% hranici ignorovaných slov, ale také kvůli dostupným operátorům mi užitečné přijde především boolean vyhledávání. Nic nám ale nebrání výsledky seřadit podle výsledků vyhledávání v přirozeném jazyce:

<?php
$search = mysql_real_escape_string($_GET["search"]);
$result = mysql_query("
    SELECT *
    FROM clanky
    WHERE MATCH(nadpis, clanek) AGAINST ('$search' IN BOOLEAN MODE)
    ORDER BY 5 * MATCH(nadpis) AGAINST ('$search') + MATCH(clanek) AGAINST ('$search') DESC
");
?>

Dotaz předpokládá, že v tabulce existují indexy FULLTEXT (nadpis), FULLTEXT (clanek) a FULLTEXT (nadpis, clanek). Dotaz by fungoval i s podmínkou MATCH(nadpis, clanek) AGAINST ('$search' IN BOOLEAN MODE), v tom případě by se ale index vůbec nepoužil a dotaz by tak byl výrazně pomalejší. Při řazení se na výskyty v nadpisu bere pětkrát větší váha než na výskyty v těle článku – při jediném indexu FULLTEXT (nadpis, clanek) by tento rozdíl nebylo možné zachytit.

Viz také Nastavení fulltextového vyhledávání MySQL.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Seznámení s oblastí, 13.9.2006, diskuse: 60 (nové: 0)

Diskuse

Hds:

Fulltextové vyhledávání v MySQL je podle mého názoru na sdíleném hostingu téměr nevyužitelné právě kvůli ft_min_word_len, která je většinou napevno nastavena na 4 a administrátoři hostingu s ní většinou nejsou ochotní hýbat. Příklad za všechny - e-shop. Zákazník bude hledat značku "LG", "AEG" atp., ale nenajde mu to nic, protože je to kratší než 4 znaky. Tam nezbývá než nasadit jednoduché LIKE. Anebo mi něco uniká? :)

ikona Jakub Vrána OpenID:

Slušný hosting by to měl být ochotný přenastavit. Pokud to nejde, tak lze pro vyhledávání kratších slov doplnit třeba takovouto podmínku:

<?php
$where
= "";
$ft_min_word_len = mysql_result(mysql_query("SHOW VARIABLES LIKE 'ft_min_word_len'"), 0, 1);
preg_match_all("~[\\pL\\pN_]+('[\\pL\\pN_]+)*~u", stripslashes($_GET["search"]), $matches);
foreach (
$matches[0] as $part) {
    if (iconv_strlen($part, "utf-8") < $ft_min_word_len) {
        $regexp = "REGEXP '[[:<:]]" . addslashes($part) . "[[:>:]]'";
        $where .= " AND (nadpis $regexp OR clanek $regexp)";
    }
}
?>

Pracuje se v kódování UTF-8 a ignorují se vyhledávací operátory.

Hds:

Díky za tip, tohle jsem ještě nezkoušel.

Venca Černík:

Hele, asi jsem trochu natvrdlej... Potřebuju aby se tam dalo vyhledávat i se 3 znakama (kdyby 2, ještě líp.) A pokud možno, tak s podobnou metodou. Prosím poraď..

ikona shuster:

Nieje v tom RegExpe chyba?
Mne to na serveri vracia chybovu hlasku "Stránku nelze zobrazit".
Ked ten riadok vykomentujem, tak mi to zbehne, ale samozerejme bez toho riadku.
Absolutne tomu RegExpu nerozumiem. Neda sa nahradit niecim inym?

ikona shuster:

Tak nakoniec to bola blba verzia php (tusim 5.1.25).
Po upgrade servera to ide normalne.

ikona tajo:

Domnívám se, že místo $where .= " AND... by tam mělo být $where .= " OR...

ikona Jakub Vrána OpenID:

To záleží na tom, jestli chci vyhledat texty se všemi zadanými slovy (AND) nebo jen některými (OR).

Lukáš Svačina:

Pokud by někomu vadilo omezení ft_min_word_len, může zkusit vytvořit fulltextové vyhledávání podle tohoto článku.
http://www.insula.cz/dali/material/fulltext101.php

Jiri Cizek:

Odkaz je nefunkční

Mike:

Zdravím, myslím že fulltext (index) funguje jen u MyISAM tabulek a nelze ho provést u transakčních INNODb .. není to dost špatné nebo je to u nové verze jinak ?

pojízdná kočka:

cituju z CHM nápovědy:
11.8.5. Full-Text Restrictions
Full-text searches are supported for MyISAM tables only.

pojízdná kočka:

ale s verzí 6.0 se prý chystají transakce i pro MyISAM, takže to může znamenat konec kompromisu mezi možností mít transakce nebo fulltextové vyhledávání..

ikona Jakub Vrána OpenID:

Transakce se pro Maria (následník MyISAM) chystají, ale cizí klíče bohužel ne.

PHX:

Zdravim. Z textu mi neni 100% jasne zda BOOLEAN MOD ma taky tu 50% hranici o vyskytu slov. Ma nebo ne? DIKY

ikona Jakub Vrána OpenID:

Režim BOOLEAN tuto hranici nemá.

PetrX:

Můžete mi někdo poradit jak vypsat tu část textu kde se nachází hledané slovo, které bylo vyhledáno v databázi pomocí fulltextového indexu. Pořád si s tím lámu hlavu a nevím jak na to.

ikona Jakub Vrána OpenID:

MySQL ti v tom nijak nepomůže, musíš si to vyřešit sám v PHP. Nebo použít vyhledávací nástroj, který úryvky nalezeného textu generovat umí, např. http://www.sphinxsearch.com/.

PetrX:

sphinxsearch se mi zdá až moc složitý, spíše by mě zajímal verze v PHP, ale nemohu najít žádný článek, který by se tomu věnoval, nevíš o nějakém?

ikona Jakub Vrána OpenID:

Nevím, ale napsal jsem si to do témat, o kterých bych časem mohl napsat.

PetrX:

V diskuzi mi bylo poradeno pouzit funkci strpos pomocí níž najdu výskyt hledaného, pak vezmu 50 znaků před a za a pomocí funkcí strpos a strrpos najít první mezeru v takovém to úrivku a poté již vybrat z úrivku přesně vybrat od začátku slova do konce posledního celého slova.
Ovšem nevím, zda-li to není až příliž kostrbaté a zbytečně složité na zatížení serveru.

ikona Petr:

Zdravím - tak co napsal jste to co jste přislíbil? ;o)

majo:

neviem vyriesit problem ako zahrnut do vyhladavania aj ciastocne vyrazy. nachadza mi iba presne vyrazy.
npr. zadam vyhladavat slovo auto a chcem aby mi vo vysledkoch vyhladavania zahrnulo aj vyraz autolak zapisany v db.

ikona Jakub Vrána OpenID:

Dá se k tomu použít operátor * v BOOLEAN vyhledávání.

AndyBrandy:

Zdravim, ako zistim relevanciu tych vysledkov? Radi to podla relevancie alebo podla coho to radi vysledky?

Dik

GrizzlyNetch:

Relevance se dá zobrazit takto:
<?php
$result
= mysql_query("SELECT (5 * MATCH(nadpis) AGAINST ('$_GET[search]') + MATCH(clanek) AGAINST ('$_GET[search]')) as relevance
    FROM cla...
"
);
?>
...k druhé otázce je asi zbytečné něco psát, když sql dotaz obsahuje přímo ORDER BY, nebo ne?:-D

Mira:

Super clanek, pomohl. Diky moc autorovi

lolek:

Zdravím, chtěl bych se zeptat jak ted vypisu ty položky vyhledane? Jsem asi natvrdlej nebo teda spis nezkusenej :). Ale zkouším následně a stále pořád to hází chybu (mysql_fetch_array(): supplied argument is not a valid).
<?php
while($row = mysql_fetch_array($result)) {
echo
$row["nadpis"];
}
?>

A rovnou bych se chtěl zeptat jestli je lepší psát kód takto bez mezery a s druhym slovem velke pismeno $nasPes nebo takto $nas_pes. Jak to normálně píšete vy Jakube?

Děkuji předem za odpověď.

ikona Jakub Vrána OpenID:

Po zavolání mysql_query() si nech vypsat chybu, ke které došlo - <?php echo mysql_error(); ?>.

Já používám $nas_pes, v objektovém programování se obvykle používá $nasPes. Stejně to je i ve většině PHP.

lolek:

Děkuju moc za radu, už vše funguje jak má. Jen bych se chtěl zeptat na poslední otázku, myslel jsem že tohle vyhledava tak, že když napíšu do vyhledavani např. slovo "dota", a v nadpise u některeho članku je slovo "dotaz", tak se to takhle najde ale ono ne.

ikona Joelp:

Dotaz by fungoval i s podmínkou MATCH(nadpis, clanek) AGAINST ('$_GET[search]' IN BOOLEAN MODE), v tom případě by se ale index vůbec nepoužil a dotaz by tak byl výrazně pomalejší.

Jak to myslíš? Proč by se neměl použít index?

ikona Jakub Vrána OpenID:

Protože jsou vytvořeny dva indexy (nadpis) a (clanek), aby se dala nadpisu přiřadit vyšší váha.

ikona Joelp:

No jestli to chápu dobře, tak pro váhu je tu řádek ORDER BY 5 * MATCH(nadpi ...

Ale pořád mi není jasné proč by se při použití zápisu MATCH(nadpis, clanek) neměl použít index oproti MATCH(nadpis) ... MATCH (clanek)

Díval jsem se i do knihy Luke Wellinga, ale nic jsem tam v tomto smyslu nenašel. Tuto informaci máš z manuálu?

ikona Jakub Vrána OpenID:

Pro fulltextové vyhledávání musí být k dispozici index se stejnými sloupci jako v dotazu. Jednak to je uvedeno v manuálu a jednak si to můžeš vyzkoušet.

Než něco napíšeš, tak si to ověř. To platí i pro http://php.vrana.cz/fulltextove-vyhledavani-sphinx.php#d-6328

ikona Joelp:

Máš na mysli, že je pořeba exitence společného indexu pro oba sloupce součastně a nestačí pouze indexovat každý sloupec zvlášť?

Rhuin:

Dobrý den, mám otázku.

Proč mi dotaz:
SELECT firma,
MATCH (firma) AGAINST ('M.K.QUATRO') AS Relevance
FROM `search_fulltext3`
WHERE
MATCH (firma) AGAINST ('M.K.QUATRO'IN BOOLEAN MODE)
HAVING Relevance > 0.2
ORDER BY Relevance DESC

hodí výsledek

M.S. Quatro s.r.o.     10.204277038574
QUATRO CH, s.r.o.     10.204277038574
QUATRO,s.r.o.     10.204277038574
QUATRO - R.I.S.K., s.r.o.     10.204277038574
HB Quatro s.r.o.     10.204277038574
QUATRO-CL s.r.o.     10.204277038574
Quatro - M, spol. s r. o.     10.089566230774
Elektro - Quatro, s.r.o.     10.089566230774
Quatro Dřevostyl, s.r.o.     10.089566230774
Quatro D Čelákovice, s.r.o.     10.089566230774
Quatro Izol s.r.o.     10.089566230774
Quatro - production, s.r.o.     10.089566230774
quatro solutions a.s.     10.089566230774
QUATRO FINANCE a.s.     10.089566230774
OPTIK-QUATRO s.r.o.     10.089566230774
METALVIS Quatro, s.r.o.     10.089566230774
QUATRO - P , SPOL.S R.O.     10.089566230774
QUATRO PLUS s.r.o., v likvidaci     10.089566230774
PIZZA - QUATRO s.r.o.     10.089566230774
L - QUATRO, spol. s r.o.     10.089566230774
Quatro Brno a.s.     10.089566230774
M.K.QUATRO, spol.s r.o.     10.089566230774
QUATRO CS,spol.s r.o.     10.089566230774

přičemž je jasné že předposlední M.K.QUATRO by mělo mít nejvyšší relevanci.

můžete mi to objasnit?
Děkuji

ikona Jakub Vrána OpenID:

Je na to odpověď přímo v článku – slova do délky ft_min_word_len se ignorují.

methew:

Nevíte někdo, jak udělat to aby když vyhledávám třeba "A6 Audi" aby mi to našlo řádky jen kde jsou obsažené obě slova a ne že mi to bude hledat ještě všechny audiny v databázi. Jak se prostě dá definovat aby vypisoval jenom řetězec se všema hledanýma slovama. Díkes

ikona Jakub Vrána OpenID:

('+A6 +Audi' IN BOOLEAN MODE). Navíc musí být nastavené ft_min_word_len nejvíce na 2.

Jan Bláha:

Zkusil jsem příklad popsaný v článku aplikovat na svoji databázi, ale bohužel varianta s OR (match .. OR match ...) nefunguje, indexy se nepoužijí. Mám nadefinované separátní fulltext indexy na obou sloupcích, ale pokud je v dotazu použit OR, ani jeden z indexů se nepoužije (podle EXPLAIN). Pokud je ve WHERE jen jedna podmínka, index se použije správně.

Zjistil jsem že to souvisí s tím že MySQL umí použít pouze 1 fulltext index v dotazu (viz např. http://capttofu.livejournal.com/8881.html).

Řešením je mít v dotazu podmínku "MATCH(nadpis, clanek) AGAINST ..." a mít indexy FULLTEXT(nadpis, clanek) pro WHERE část a FULLTEXT(nadpis) a FULLTEXT(clanek) pro výpočet relevance. Při velkých tabulkách tyto indexy ovšem značně bobtnají.

Rád bych zde proto zkonzultoval, zda není ještě jiné řešení nebo zda se dá vymyslet jiný způsob, při kterém by se dala použít podmínka OR tak jak je v příkladu.

Vše testováno na MySQL 5.0.51a

godder:

Dekuji nescetnekrat, dva dny jsem stravil resenim a prepisovanim dotazu, proc se mi nepouzivaji indexy, kdyz pouzivam OR. Myslim, ze by na to melo byt v clanku vyrazne upozorneni. Kazdopadne jeste jednou diky!

ikona Jakub Vrána OpenID:

Díky za upozornění, opravil jsem to.

Perry:

Neporadil by mi někdo, jak "obejít" stopwords ? Bohužel je nijak nemůžu na hostingu vypnout. Něco podobného jako s min. délkou slova, jako psal Jakub Vrána ve druhém příspěvku by se mi hodilo

Logik:

Jednoduše - připojit podtržítko za každý slovo...

Suto:

to ale vobec nefunguje

ked chcem hladat ceske mesto Most

tak to berie ako stopword most a podtrzitko s tym nic neurobi

davEsim:

Dotaz funguje bezvadně, jen by podle mne mělo byt v ORDER BY descendentní řazení...

ikona Jakub Vrána OpenID:

Díky za upozornění, opravil jsem to.

miki:

při hledání mi to najde pouze slova stejné velikosti písmen dá se to nějak obejít aby byl stejný výsledek pro "TEST" i "test" ?

ikona Jakub Vrána OpenID:

Záleží na způsobu porovnávání prohledávaného sloupce. Viz http://php.vrana.cz/mysql-4-1-kodovani.php a také http://php.vrana.cz/vyhledani-textu-bez-diakritiky.php. Porovnávání nerozlišující velikost písmen končí na _ci (case-insensitive).

ikona Keilew:

Zdravím, velmi bych uvítal radu.
Mám dva různé vyhledávací řetězce, oba by měly najít slovo "černobílá", avšak druhý toto slovo při vší snaze nenajde (jde tradičně o znaky ž, š, č, ř):
1) SELECT * FROM `sklad` WHERE `popis1` LIKE '%cernobílá%' COLLATE utf8_general_ci
2) SELECT * FROM `sklad` WHERE MATCH (`popis1`) AGAINST ('cernobílá' COLLATE utf8_general_ci IN BOOLEAN MODE)

Mají pro to nějaký pádný důvod? COLLATE evidentně nereaguje. Avšak pak je fulltext v podstatě k ničemu...

Podotázka - najít "automobil" pomocí MATCH AGAINST ze slova "auto" jde snadno, stačí použít 'auto*'. Ale dá se najít takto "superauto"? Řetězec '*auto' nereaguje...

ikona Jakub Vrána OpenID:

Pokud chceš fulltextově vyhledávat bez diakritiky a nastavení COLLATE utf8_general_ci rovnou v definici sloupce se nedá použít, tak nezbývá nic jiného, než si data kopírovat do nového sloupce určeného pro vyhledávání (nejlépe automaticky pomocí triggeru).

Hvězdička slouží jen pro pravostranné rozšíření.

Viz také http://php.vrana.cz/vyhledani-textu-bez-diakritiky.php.

Michal:

Řeším problém...

potřebuje aby když napíšu „xander“, aby mi to v mysql fulltext našlo i „Alexander“

(když napíšu Alex* tak to Alexander najde, to je v pohodě)

píšete, že: Hvězdička slouží jen pro pravostranné rozšíření.

Existuje i nějaké levostranné řešení??

Díky

ikona Jakub Vrána OpenID:

Operátor pro levostranné rozšíření ve fulltextovém vyhledávání MySQL neexistuje.

Michal:

jj, to vím, že neexistuje :-) ale potřebuji to nějak obejít a to ve fulltextu, aby to bylo rychlé. Máme obchod, kde je například foťák:

SONY DSCW130B.CEE9 Cyber-Shot 8,1MPix, 4x zoom...

no a zákazník si pamatuje, že to je třeba sony a 130B jenže ono to je před tou stotřicítkou ještě dscw takže mu to najde tisíce jinýh výrobků sony :-(

ikona v6ak:

1. Nějakým způsobem doplnit vhodné oddělovače při vkládání
2. Při čtení je odstranit

Vincenzo:

Prosím poraďte, co dělám špatně.
tabulka je celá v UTF-8general-ci

zkoušel jsem mnoho způsobů např. tohle
SELECT * FROM `wd_zapisy` WHERE MATCH(nadpis) AGAINST ('neznámé' IN BOOLEAN MODE)

ale diakritiku to prostě nenajde :-(

Martin:

celý problém, aby to hledalo i malými písmeny a i části řetězce jsem vyřešil takto:

    $hledej=strtolower($hledej);
    $query ="Select * from tabulka where lower(`nazev`) like '%$hledej%' or lower(`vyrobce`) like '%$hledej%'  ";

MP:

Mam trochu iny problem s "fulltextovym" hladanim, potrebujem hladat v zaciatkoch slov, bez rozlisovania diakritiky teda s collate utf8_general_ci LIKE nieje riesenim a REGEX collate ignoruje.

'Koněvova' RLIKE '[[:<:]]Kon'   == true
'Koněvova' RLIKE '[[:<:]]Kone'   == false

Fulltext pouzit nemozem. Pomoze dakto?

ikona Jakub Vrána OpenID:

Dělat to na úrovni MySQL je dost chůze po tenkém ledě, viz varování na http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp (operátor funguje na úrovni bajtů).

Dalo by se to vyřešit tak, že by se pomocí LIKE vybrali kandidáti na shodu a ta by se pak potvrdila v PHP. Případně pokud jsou začátky slov nějak omezeny (např. víme, že začínají mezerou), tak použít:

s LIKE 'Kone%' OR s LIKE '% Kone%'

MP:

Vdaka o like som vedel bohuzial tych oddelovacov tam moze byt viac a dopredu presne neviem ake budu. Rozmyslam ako by sa to vyriesilo na strane php, aby to vedelo overit bez ohladu na diakritiku, ako ano oddiakritikovat a porovnat, ale to sa mi zda take nepekne zlozite. Asi tam hodim tych par like a uvidim ako budu postupne pribudat oddelovace. Este raz diki za odpoved

Vložit příspěvek

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2016 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.