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 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 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ší.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.
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á? :)
Jakub Vrána 
:
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.
Jakub Vrána : 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ď..
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?
shuster: shuster:
Tak nakoniec to bola blba verzia php (tusim 5.1.25).
Po upgrade servera to ide normalne.
shuster: 
tajo:
Domnívám se, že místo $where .= " AND... by tam mělo být $where .= " OR...
tajo: Jakub Vrána :
To záleží na tom, jestli chci vyhledat texty se všemi zadanými slovy (AND) nebo jen některými (OR).
Jakub Vrána : 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í.. Jakub Vrána :
Transakce se pro Maria (následník MyISAM) chystají, ale cizí klíče bohužel ne.
Jakub Vrána : PHX:
Zdravim. Z textu mi neni 100% jasne zda BOOLEAN MOD ma taky tu 50% hranici o vyskytu slov. Ma nebo ne? DIKY Jakub Vrána :
Režim BOOLEAN tuto hranici nemá.
Jakub Vrána : 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. Jakub Vrána :
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/.
Jakub Vrána : 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? Jakub Vrána :
Nevím, ale napsal jsem si to do témat, o kterých bych časem mohl napsat.
Jakub Vrána : 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.
Petr:
Zdravím - tak co napsal jste to co jste přislíbil? ;o)
Petr: 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.
Jakub Vrána :
Dá se k tomu použít operátor * v BOOLEAN vyhledávání.
Jakub Vrána : 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ěď.
Jakub Vrána :
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.
Jakub Vrána : 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.
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?
Joelp: Jakub Vrána :
Protože jsou vytvořeny dva indexy (nadpis) a (clanek), aby se dala nadpisu přiřadit vyšší váha.
Jakub Vrána : 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?
Joelp: Jakub Vrána :
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
Jakub Vrána : 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ášť?
Joelp: 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
Jakub Vrána :
Je na to odpověď přímo v článku – slova do délky ft_min_word_len se ignorují.
Jakub Vrána : 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 Jakub Vrána :
('+A6 +Audi' IN BOOLEAN MODE). Navíc musí být nastavené ft_min_word_len nejvíce na 2.
Jakub Vrána : 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! Jakub Vrána :
Díky za upozornění, opravil jsem to.
Jakub Vrána : 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 hodiloLogik:
Jednoduše - připojit podtržítko za každý slovo...Suto:
to ale vobec nefungujeked 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í... Jakub Vrána :
Díky za upozornění, opravil jsem to.
Jakub Vrána : 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" ? Jakub Vrána :
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).
Jakub Vrána : 
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...
Keilew: Jakub Vrána :
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.
Jakub Vrána : 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
Jakub Vrána :
Operátor pro levostranné rozšíření ve fulltextovém vyhledávání MySQL neexistuje.
Jakub Vrána : 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 :-(
v6ak:
1. Nějakým způsobem doplnit vhodné oddělovače při vkládání
2. Při čtení je odstranit
v6ak: 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?
Jakub Vrána :
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%'
Jakub Vrána : 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 odpovedPavel:
Zdravím,mám jeden problém. Mám to napsané správně? Snažím se vyhledat článek, ale píše to: Nebylo nic nalezeno.
Zde je kód:
<?php
public function hledejClanky() {
$hledani = mysqli_real_escape_string($this->con, $_GET['search']);
$query = "SELECT * FROM clanky WHERE MATCH (nazev, text) AGAINST ('$hledani' IN BOOLEAN MODE)";
$resu = mysqli_query($this->con, $query);
$radku = $resu ? mysqli_affected_rows($this->con) : 0;
if ($radku)
{
while ($vys = mysqli_fetch_assoc($resu))
{
echo('<h2>' . $vys['nazev'] . '</h2>');
}
}
else
{
echo('<h2>Nebylo nic nalezeno.</h2>');
}
}
?>
Jakub Vrána :
mysqli_affected_rows vrací počet řádků, které byly posledním dotazem změněné. Počet nalezených řádků vrací mysqli_num_rows.
$vys['nazev'] je dobré zabalit do htmlspecialchars().
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 