[MySQL] Eigen searchindex doorzoeken

[.Johnny]

Ik wil graag even een oplossing tegen het licht van wat meer experts houden, omdat ik betwijfel of het wel de meest optimale en juiste is. Het gaat om een searchindex die ik zelf heb aangelegd, omdat de fulltext searchindex van mysql zelf me niet bevalt.

Hier is een zwaar versimpeld voorbeeld van een te indexeren tabel:

documents

d_id	text
1	a b c
2	a b
3	a

de index ziet er dan zo uit:

field	d_id
1	a
1	b
1	c
2	a
2	b
3	a

Hoe maak je nu een generieke query om op meerdere velden tegelijk te matchen? Bijvoorbeeld: de query is "a b"; hoe ziet de query eruit die lijdt tot de juiste antwoorden: 1 + 2? Met andere woorden: wat zijn de d_id's die zowel een field "a" als een field "b" in de index hebben?

Wat ik nu heb gedaan is dit:

code:

SELECT * FROM documents d

met daarachter steeds een JOIN:

code:

JOIN document_index as di ON d.d_id=di.d_id AND di.field='<query part>'

wat voor 2 delen (a EN b) resulteert in de volgende query:

code:

SELECT * FROM documents d
JOIN document_index as di1 ON d.d_id=di1.d_id AND di1.field='a'
JOIN document_index as di2 ON d.d_id=di2.d_id AND di2.field='b'

Dit werkt goed, en doordat je per zoek-deel je ook 1 deel aan de query toe kunt voegen, is dit makkelijk in je programmatuur te plaatsen. Ik heb behoorlijk wat af gegoogled, maar heb nergens kunnen vinden of dit nou de ideale manier is om een dergelijke query op te pakken. Als je een OR wil in de query wordt het trouwens een stuk eenvoudiger om een query te maken (gewoon met een WHERE field IN (<query parts>)) maar dat is nu even niet aan de orde. Dus mijn vraag; is dit een goede manier, of zijn er betere manieren om dit te doen?

[Janoz]

Je zou ook wel de OR variant kunnen nemen en vervolgens het aantal hits kunnen tellen. Vervolgens filter je met HAVING alle resultaten eruit die minder hits hebben dan het aantal 'query parts'.

[Nick_S]

Wil je dit perse in je database doen? Wij (en de frameworks die we gebruiken) lossen dit meestal op door Lucene in te zetten als indexer.

[.Johnny]

@Janoz;
daar heb ik inderdaad ook aan gedacht, maar is dat efficienter? Ik loop daar zelf ook tegen nog wat andere dingen aan, omdat ik al een andere GROUP BY heb in de query die in werkelijkheid dus al een stuk complexer is (nog meer joins met andere tabellen, om zo een subset te doorzoeken ipv alle documenten)

@Nick_s;
Dat klinkt mij wel een beetje in de oren als overhead; zeker ook icm bovenstaande; de extra joins die ik nodig heb om een subset te doorzoeken. In feite zijn het niet documenten die ik doorzoek, maar auteursnamen, dus de velden zijn niet heel lang. dat doet me ook vermoeden dat Lucene meer overhead geeft dan dat het resultaat oplevert

[Nick_S]

Misschien overhead, maar jouw model heeft een sterke neiging naar explosieve groei. Je gaat in dit simpele voorbeeld voor 3 records al 6 nieuwe aanmaken.

Ook als je wilt gaan zoeken met door gebruikers ingevulde termen, kun je hier problemen mee krijgen. Spelfouten, speciale tekens (René vs. Rene).

Wat als gebruikers uitgebreider willen gaan zoeken (Soort google, met and / or termen, wildcards, combinaties) kan je logica die je SQL gaat opbouwen erg complex worden.

Dus of het overkill is of niet laat ik aan jezelf over, dat ligt eraan waar je het voor wilt gaan inzetten en of je nog uitbreidingen verwacht.

[leuk_he]

Als je hardcore je queries wilt optimaliseren dan ga je uiteraard daar met relatief echte data en en of verschillende storage enginges en echt hardware aan de gang. Over optimalisatie zijn bergen documenten over te vinden, te bgeinnen bij.

http://dev.mysql.com/doc/...ptimizing-the-server.html

Het maakt nogal uit hoeveel rijen het over gaat en hoeveel geheugen je hebt enzo. Tenminste, aangezien full text voor jou niet voldoet neem ik aan dat de vereisten nogal specifiek zijn.

Iets concreter:

WHERE field IN (<query parts>)) IS vaak niet optimaal als de set die uit "IN" komt groot is.

Je join / IN kun je ook nog omschrijven naar een "exists" qeurie he?

[Voutloos]

leuk_he schreef op donderdag 04 juni 2009 @ 13:39:
Het maakt nogal uit hoeveel rijen het over gaat en hoeveel geheugen je hebt enzo. Tenminste, aangezien full text voor jou niet voldoet neem ik aan dat de vereisten nogal specifiek zijn.

mysql full text is onmogelijk snel te krijgen bij een bepaalde hoeveelheid rijen. Generalisering, maar boven de 100k rijen ga je al heel gauw balen van deze trage, amper te tweaken black box. Gespecialiseerde engines als Lucene en sphinx zijn dan je beste vriend.

WHERE field IN (<query parts>)) IS vaak niet optimaal als de set die uit "IN" komt groot is.

Je join / IN kun je ook nog omschrijven naar een "exists" qeurie he?

IN() is vaak onverwacht traag, maar dat geld ook voor EXISTS. Juist de beste trucs op dit vlak gebruiken joins. Dit is een generalisatie en dergelijke tweaks zjn vaak redelijk complex en zouden zomaar overbodig kunnen worden bij een nieuwe mysql (extra kans juist met de huidige mysql optimizer wijzigingen).

[ Voor 10% gewijzigd door Voutloos op 04-06-2009 14:17 ]

[.Johnny]

@Nick_s: 6 records voor 3 bestaande; ja dat klopt, maar ik neem aan dat Lucene ook met tf/idf werkt en daarom ook zo'n index maakt. Die is wel vereist om dat te kunnen doen. Dus of ik die index maak of lucene, dat maakt dan ook niet meer echt uit lijkt me. Ook normalizatie (iconv + TRANSLIT to the rescue) hou ik liever zelf in de hand, en boolean searches (anders dan zoals nu standaard met AND gedefinieerd) hoef ik ook niet, dus dat scheelt ook weer.

@leuk_he; uiteraard geldt dat voor de uiteindelijke optimalisatie, maar ik vroeg me gewoon af of iemand al eens eerder met een dergelijk bijltje heeft gehakt en wat de bijpassende nette query is. De optimalisatie komt daarna wel. Ik hoop ook op deze manier wat vast te laten leggen voor anderen die met een soortgelijk probleem zitten, aangezien een google zoektocht naar een dergelijk query vervuild is door mysql's fulltext search queries ipv zelfgebakken indexen.
Fulltext voldoet voor mij vooral niet functioneel; om bijvoorbeeld te matchen op korte strings (<2 tekens) moet je mysql al opnieuw configureren, dat kun je voor zover ik weet niet direct in de key doen. Daarbij geeft zelf een index maken veel meer inzicht in wat je doet en hoe je resultaten tot stand komen.

[Voutloos]

.Johnny schreef op donderdag 04 juni 2009 @ 15:34:
6 records voor 3 bestaande; ja dat klopt, maar ik neem aan dat Lucene ook met tf/idf werkt en daarom ook zo'n index maakt.

Dat klopt, maar dat is slechts een deel van het wiel dat je nu opnieuw aan het uitvinden bent.