MySQL Full-text Search

4 naročniki

Opis baze:
Imam več tabel, vsaka posebej ima par kolumn, in recimo v posamezni tabeli je na določene kolumne dodan index fulltext in sicer vsak posebej. Recimo: tabela: avtor, fulltext index na 'ime' in fulltext index na 'priimek'. torej, kaj želim je v bistvu tale krača:

SELECT vsebina.id, avtor.ime, avtor.priimek, beseda.naziv, podnaslov.podnaslov, MATCH(vsebina.besedilo,avtor.ime,avtor.priimek,podnaslov.podnaslov,beseda.naziv) AGAINST('podeželje') AS score FROM
`delo_vsebina` LEFT JOIN `vsebina` ON delo_vsebina.vsebina_id=vsebina.id LEFT JOIN `delo` ON delo_vsebina.delo_id=delo.id LEFT JOIN delo_avtor ON delo_avtor.delo_id=delo.id LEFT JOIN avtor ON
delo_avtor.avtor_id=avtor.id LEFT JOIN delo_beseda ON delo_beseda.delo_id=delo.id LEFT JOIN beseda ON delo_beseda.beseda_id=beseda.id LEFT JOIN delo_podnaslov ON delo_podnaslov.delo_id=delo.id LEFT
JOIN podnaslov ON delo_podnaslov.podnaslov_id=podnaslov.id WHERE MATCH(vsebina.besedilo,avtor.ime,avtor.priimek,podnaslov.podnaslov,beseda.naziv) AGAINST('podeželje')

Ni lepa, vendar bi morala delovati.

vsebina.besedilo, avtor.ime itd. (vsi v MATCH) imajo FULLTEXT index v posamezni tabeli.
Je tole sploh pravilen način iskanja, ali bi moral narediti kako temporary tabelo z rezultati le te, pa potem tam nastaviti indexe na pravilne kolumne in nato pognati search nad tisto tabelo?

Skratka error dobim: "#1210 - Incorrect arguments to MATCH"

Sem tudi repairal tabele, če bi slučajno bil to problem, vendar je napaka še vedno tu...

Zanimivo da recimo če dam pa 1 argument MATCH-u, recimo samo "avtor.ime" ali samo "vsebina.besedilo", pa SQL spelje...

Alternativa temu bi bila pač da MATCH pošljem samo čez (vsebina.besedilo) in potem delam naprej OR LIKE ('%keyword%')nad ostalimi kolumni... vendar če se le da, ne bi tega počel.

Ne bo delovalo na tak način, ki ga poskušaš, fulltext search lahko izvajaš le po podatkih v eni tabeli.

Varjanta je, da delaš potem MATCH (vsebina.besedilo) OR MATCH (avtor.ime) OR ... ampak potem sem uporabil kar LIKE, ker načeloma je tako... score je važen nenazadnje le ob številu pojavitev v besedilu, če pa je beseda kjerkoli drugje najdena pa predvidevam da je to direkten zadetek (se pravi če je bilo iskanje po avtorju in je nekdo vpisal ime avtorja za iskanje) in je takoj rezultat 100%. Me pa malce skrbi, kako bo tale query delal, ko bom imel za 10 let dokumentov od fakultete importanih... Verjetno bo le čas pokazal in EXPLAIN :) Hvala za odgovor.

Ojej, ne uporabljaj LIKE za te zadeve, ti lahko že kar zdaj povem kar ti bo povedal EXPLAIN, nič dobrega :) Za search lahko poskusiš uporabiti Sphinx, super zadevica.

Za začetek je hitrejš "narejen" tudi Zend Lucene, ne super ampak fajn zadevca.

@Vini: ko si omenjal da naj ne bi naredil z LIKE-om sem mislil da bi naredil še vedno MATCH po tabeli ki ima besedila, se pravi "vsebina", katere je in bo največ, le za ostale manjše tabelice naj bi uporabil LIKE... primer kako sem naredil zaenkrat:

$addon = "SELECT DISTINCT vsebina.id, MATCH(vsebina.besedilo) AGAINST('".$keyword."') AS score FROM `delo_vsebina` LEFT JOIN `vsebina` ON delo_vsebina.vsebina_id=vsebina.id LEFT JOIN `delo` ON delo_vsebina.delo_id=delo.id LEFT JOIN delo_avtor ON delo_avtor.delo_id=delo.id LEFT JOIN avtor ON delo_avtor.avtor_id=avtor.id LEFT JOIN delo_beseda ON delo_beseda.delo_id=delo.id LEFT JOIN beseda ON delo_beseda.beseda_id=beseda.id LEFT JOIN delo_podnaslov ON delo_podnaslov.delo_id=delo.id LEFT JOIN podnaslov ON delo_podnaslov.podnaslov_id=podnaslov.id WHERE CONCAT_WS(' ', avtor.ime, avtor.priimek, beseda.naziv, podnaslov.podnaslov) LIKE ('%".$keyword."%') OR MATCH(vsebina.besedilo) AGAINST('".$keyword."')";

Še vedno misliš da bo obupen performance tega SQL-a?

Vsekakor pa vem da bo potrebno "search" spremeniti v prihodnosti in uporabiti nekaj drugega kot MySQL-ov search... SphinX sem že gledal in je precej luštna zadeva, ki sama kešira v fajlih, dejansko pa se v sintakso le-tega še nisem imel časa poglobiti.

@Roky: Trenutna aplikacija je v codeigniterju, tako da za menjavo frameworka je malce prepozno :) Predvsem zato ker je že čisto zaključena, časa pa je samo še ta teden... :)

bl4ckb1rd, če podatke prej dovolj dobro optimizirano sfiltriraš, da potem LIKE delaš le preko manjših result setov, potem bi mogoče še nekako zneslo. Ker pa ti med pogoje postavljaš OR, pa nisi nič kaj dosti dosegel. Poskusi, napolni bazo s čim več dummy teksta, pa poglej, kako dolgo se ti izvaja query. Mislim, da vsekakor predolgo, ubil si boš strežnik :)

@bl4ckb1rd, menjava framework-a ni potrebna. V codeignator dodaš helper, class, model ... karkol, kjer rečeš:

// indexer se zna obesit zato k ma premal rama, dajmo mu ga več
set_time_limit(60);
ini_set('memory_limit','64M');

require_once 'Zend/Search/Lucene.php';

$indexDir = 'pot/do/indexa';
try {
    $index = Zend_Search_Lucene::open($indexDir);
    $this->setIndex($index);
} catch (Exception $e) {
    $this->setIndex();
}

Zend_Search_Lucene_Analysis_Analyzer::setDefault(new Zend_Search_Lucene_Analysis_Analyzer_Common_Utf8_CaseInsensitive());

//RM: Just to be 100% sure that UTF-8 encoding is used
Zend_Search_Lucene_Search_QueryParser::setDefaultEncoding('UTF-8');
$query = Zend_Search_Lucene_Search_QueryParser::parse($q, 'UTF-8');
$hits = $this->getIndex()->find($query, 'area', SORT_STRING, 'score', SORT_NUMERIC, SORT_DESC);

foreach($hits as $hit) {
        if (!$this->filterHit($hit)) continue;
        $i++;
        ;

        $rHits[] = array(
        'area'=>$hit->area,
        'title'=>$hit->title,
        'url'=>$hit->url,
        'full_url'=>$this->getFullUrl($hit),
        'description'=> $hit->description,
        'score'=>sprintf("%01.1f",$hit->score*100)
        );
}

Najlepša hvala za tele response, več kot pomagalo bo. Verjetno bom uporabil kar Lucene, glede na to da mi je Roky že namignil dejansko kako naj bi izgledalo, super res. Prišparali ste mi precej časa in dela.

@bl4ckb1rd: Še tole, v ozadju moraš indekser nafilat in sicer:

// indexer se zna obesit zato k ma premal rama, dajmo mu ga več
set_time_limit(100);
ini_set('memory_limit','64M');

require_once 'Zend/Search/Lucene.php';

//RM: CaseInsensitive is used, check http://framework.zend.com/manual/en/zend.search.lucene.charset.html for more info
Zend_Search_Lucene_Analysis_Analyzer::setDefault(new Zend_Search_Lucene_Analysis_Analyzer_Common_Utf8_CaseInsensitive());
self::$works = Application::indexWorks();

$indexDir = Application::getIndexPath();
try {
    $index = Zend_Search_Lucene::open($indexDir);
} catch (Zend_Search_Lucene_Exception $e) {
    $index = Zend_Search_Lucene::create($indexDir);
}

$doc = new Zend_Search_Lucene_Document();

$encoding = 'utf-8';
$id = 'content'.$id;

$doc->addField(Zend_Search_Lucene_Field::UnIndexed(self::F_CREATED, time()));

$doc->addField(Zend_Search_Lucene_Field::Keyword(self::F_AREA, $area, $encoding));
$doc->addField(Zend_Search_Lucene_Field::Keyword(self::F_ID, $area . $id, $encoding));
$doc->addField(Zend_Search_Lucene_Field::Keyword(self::F_LANG, $lang, $encoding));

$doc->addField(Zend_Search_Lucene_Field::Text(self::F_TITLE, $title, $encoding));
$doc->addField(Zend_Search_Lucene_Field::Text(self::F_URL, $url, $encoding));
$doc->addField(Zend_Search_Lucene_Field::Text(self::F_DESCRIPTION, $description, $encoding));

$doc->addField(Zend_Search_Lucene_Field::UnStored(self::F_CONTENT, $content, $encoding));

$doc->addField(Zend_Search_Lucene_Field::Text(self::F_PORTAL, $portal, $encoding));

$index->addDocument($doc);

P.S: Priporočam ti, da vsakič, ko gradiš nov index vedno pobriši celoten index in na novo dodaj. Meni se zdi, da veliko hitreje dela + več zadetkov meče, kot če editaš zadeve.