[MYSQL] Geavanceerde zoekfunctie

[Verwijderd]

Ik heb de volgende query die achter een zoekfunctie op mijn website in aanbouw zit:

SQL:

SELECT DISTINCT laatste_berichtdatum as datum,
  forumonderwerpen.forumonderwerp_nummer,
  forumonderwerpen.forumgroep_nummer,
  aantal, forumonderwerp_naam 

FROM forumonderwerpen
INNER JOIN forumonderwerp_regels ON forumonderwerp_regels.forumgroep_nummer = forumonderwerpen.forumgroep_nummer AND
forumonderwerp_regels.forumonderwerp_nummer = forumonderwerpen.forumonderwerp_nummer 

WHERE (((Tekst LIKE '%test%' OR Forumonderwerp_naam LIKE '%test%'))) AND
  (((Tekst LIKE '%tekst%' OR Forumonderwerp_naam LIKE '%tekst%'))) AND
  (((Tekst LIKE '%meertekst%' OR Forumonderwerp_naam LIKE '%meertekst%')))

Dit is een voorbeeld van een query die opgebouwd wordr naar aanleiding van het zoeken op de termen: 'test' 'tekst' en 'meertekst'. Er wordt gezocht in het veld tekst, dit bevindt zich in de tabel forumonderwerp_regels. Bij elke forumonderwerp kunnen logischerwijs meerdere forumonderwerp_regels horen.

Op zich werkt de query wel, maar deze doet niet helemaal wat ik wil. Ik wil namelijk alle onderwerpen terugzien waarin de zoektermen allemaal terugkomen in tenminste één van de berichten van dat onderwerp. Met bovenstaande query krijg ik echter alleen de onderwerpen terug die een bericht bevatten waarin alle zoektermen voorkomen. Alle zoektermen moeten dus in hetzelfde bericht zitten. Ik wil dus echter dat een onderwerp ook in het resultaat voorkomt als bij de ene zoekterm in het eerste bericht van het onderwerp zit, de tweede in het zesde bericht van het onderwerp en de derde zoekterm in het laatste bericht.

Ik zag dat dit wel gebeurt als ik op dit forum een zoekactie start waarbij ik aangeef dat alle termen voor moeten komen. Daarom hoop ik langs deze weg de oplossing te vinden. Iemand die me kan aangeven hoe ik dit voor elkaar kan krijgen? Het liefst ook nog zo efficient mogelijk qua snelheid want de database zal al snel een behoorlijke omvang krijgen.

Ik heb er over zitten denken om (bij het opslaan van nieuwe berichten) alle teksten in het veld "tekst" in forumonderwerp_regels ook nog eens achter elkaar geplakt op te nemen in forumonderwerpen (in een apart veld dus) zodat ik dan in dit samengeplakte veld in forumonderwerpen kan zoeken. Dit zal wellicht voor het zoeken snel het gewenste resultaat geven, maar dan sla ik alle tekst twee keer op in de database en dat lijkt me nou niet bepaald wenselijk. Is er een betere oplossing voor dit probleem?

[ Voor 4% gewijzigd door curry684 op 06-05-2005 12:12 . Reden: code in [code] ]

[Spider.007]

Dit is geen software maar een programmeerprobleem

SA > PW

[simon]

Wat dacht je van fulltext?

[NMe]

Waarom een join van twee tabellen twee velden?

Je query is trouwens vrij onleesbaar. Kun je hem even netjes formatteren met wat enters waar enters kunnen en door er code tags omheen te zetten?

[ Voor 61% gewijzigd door NMe op 03-05-2005 21:10 ]

[ripexx]

Zoals Simon al aangeeft is de fulltext search optie van MySQL een mogelijkheid. Daarnaast kan je zelf een index maken en een zoektabel met daarin links naar posts en/of topics. Of maak gebruik vane en extrene zoekmachine zoals omega. Daarnaast is dit qua preformance killing. De grote hoeveelheid LIKE '%iets%' searches kunnen niet geindexeerd worden.

Als je dan toch met wil zoeken met wildcrads aan de voorkant en achterkan kan je een tweede zoek kolom defineren. Elk woord wordt dat normaal en inverse opgeslagen waarna je deze twee zoekacties kan combineren. Voordele hiervan is dat je search index gebruiekn. Bij 100 of 1000 berichten maakt dat wel verschil maar zal het niet merkbaar zijn, maar zodra je over duizenden of miljoene reocrds hebt wordt het ondoenlijk.

Hoe goed MySQL fulltext functioneerd bij grote record sets weet ik niet maar de functionaliteit is zeker in ver 3 nog beperkt.

[Verwijderd]

Ik had begrepen dat fulltext search alleen werkt in combinatie met myisam tabellen en niet met innodb. Ik gebruik alleen innodb, omdat het om grote hoeveelheden gegevens gaat die bovendien frequent benaderd worden. In zo'n geval geniet innodb toch duidelijk de voorkeur boven myisam is het niet?

Iemand anders vertelde me dat de zoektijd in de database niet zo veel uit maakte, omdat het versturen van de gegevens naar de client naar verhouding toch het meeste tijd kost. Aan de reactie van ripexx te zien is dit kennelijk niet zo. Ik begrijp dat het vrij gebruikelijk is om gegevens dubbel op te slaan in een zoektabel om zo de verwerkingstijd te bevorderen of interpreteer ik dat nu verkeerd?

Verder wordt opgemerkt dat ik niet zoveel LIKE operators moet gebruiken, maar hoe moet ik het dan wel doen. Wat is een betere oplossing als ik werk met innodb tabellen? Ik weet niet beter dan dat je in zo'n geval zoekt met een query met voor elke zoekterm een like-operator. Als ik dan tegelijkertijd op onderwerp en berichtinhoud wil zoeken dan heb ik voor elke term zelfs twee like-operators nodig. Hoe doe ik dit efficienter?

Op verzoek de query even opnieuw:

SQL:

SELECT DISTINCT laatste_berichtdatum as datum,
  forumonderwerpen.forumonderwerp_nummer,
  forumonderwerpen.forumgroep_nummer,
  aantal, forumonderwerp_naam 

FROM forumonderwerpen
INNER JOIN forumonderwerp_regels ON forumonderwerp_regels.forumgroep_nummer = forumonderwerpen.forumgroep_nummer AND
forumonderwerp_regels.forumonderwerp_nummer = forumonderwerpen.forumonderwerp_nummer 

WHERE (((Tekst LIKE '%test%' OR Forumonderwerp_naam LIKE '%test%'))) AND
  (((Tekst LIKE '%tekst%' OR Forumonderwerp_naam LIKE '%tekst%'))) AND
  (((Tekst LIKE '%meertekst%' OR Forumonderwerp_naam LIKE '%meertekst%')))

[ Voor 5% gewijzigd door Creepy op 04-05-2005 08:50 ]

[Creepy]

flbos: de vraag was niet om je code opnieuw te geven, maar om het in [code] tags neer te zetten. Ik heb je post voor je opgeleukt

Overigens staan hier: http://forums.mysql.com/read.php?22,12604,12604#msg-12604 wat work-a-rounds om met innodb te draaien en toch fulltext search te kunnen doen (lees: data repliceren in een myisam en daarop een fulltext doen....), maar of dat voor je bruikbaar is.. ?

[D4Skunk]

Je moet dit met een subselect oplossen (als MYSQL dit ondersteunt ?):

SQL:

SELECT 
  laatste_berichtdatum as datum,
  forumonderwerp_nummer,
  forumgroep_nummer,
  aantal, 
  forumonderwerp_naam 
FROM 
  forumonderwerpen 
WHERE forumonderwerp_naam like '%test%'
   OR forumonderwerp_naam like '%tekst%'
   OR forumonderwerp_naam like '%meertekst%'
   OR forumonderwerp_nummer in (
      SELECT 
         forumonderwerp_nummer 
      FROM 
         forumonderwerp_regels
      WHERE tekst like '%test%'
         OR tekst like '%tekst%'
         OR tekst like '%meertekst%'
         )

Dit zal dus echt wel ENORM traag werken bij een grote hoeveelheid gegevens...

[ripexx]

InnoDB heeft met name bij grote tabellen met relatief veel inserts en updates nut omdat je dan row level locking hebt ipv een complete table lock. Dus een sessie tabel oid heeft er meer voordeel van dan een stamtabel welke nauwelijks mutaties hoeft te verwerken.

Zoals creepy aal aangeeft is er een workaround maar ik zou er niet zo snel aanbeginnen.

opzich is er niet zo heel veel mis met een LIKE search maar als je ook wildacars aan het begin van je zoekstring wil gebruiken kan MySQL niet gebruik maken van indexen wat bij grote tabellen echt preformance gaat kosten. Je moet dan niet raar staan te kijken dat een query enkele seconden duurt.

Maar om hoeveel records in de forumonderwerp tabel? Want als je het hier hebt over een 100 tot 1000 berichten kan het wel, maar als je het hebt over een database als GoT waarbij er > miljoen reocrds zijn waar op gezocht kan worden is het een ander verhaal. Niet voor niets is er uit geweken naar een externe zoekdatabase met eigen software, speciaal geschreven voor dit soort zoekacties.

[Verwijderd]

Bedankt voor alle reacties, ik begrijp het inmiddels van die code tags

Het gaat nu nog om een beperkt aantal records, maar dat zal in de toekomst gaan veranderen. Als ik het goed begrijp dan komt het er op neer dat wanneer je fatsoenlijke zoekfunctionaliteit in een grote database wilt maken dat je dan eigenlijk niet ontkomt aan het gebruiken van een externe zoekdatabase?

Aan zo'n extere zoekdatabase zullen ongetwijfeld kosten verbonden zitten of heb ik dat verkeerd? Hoe heeft Got dit bijvoorbeeld geregeld? Welke software is er voor nodig en waar haal je die vandaan?

We zijn inmiddels wel een beetje afgedwaald van het originele onderwerp van dit topic, maar jullie antwoorden zijn wel zeer bruikbaar voor me (ik hoop dat iemand anders er ook nog iets aan heeft)!

[ripexx]

Verwijderd schreef op donderdag 05 mei 2005 @ 17:26:
Bedankt voor alle reacties, ik begrijp het inmiddels van die code tags

Het gaat nu nog om een beperkt aantal records, maar dat zal in de toekomst gaan veranderen. Als ik het goed begrijp dan komt het er op neer dat wanneer je fatsoenlijke zoekfunctionaliteit in een grote database wilt maken dat je dan eigenlijk niet ontkomt aan het gebruiken van een externe zoekdatabase?

Jep, daar komt het in pricipe wel op neer, zeker met MySQL. ik weet niet hoe Oracle en SQl server met fulltext searches omgaan maar het zou me niets verbazen als deze bij grote datasets ook preformance problemen gaan krijgen.

Aan zo'n extere zoekdatabase zullen ongetwijfeld kosten verbonden zitten of heb ik dat verkeerd? Hoe heeft Got dit bijvoorbeeld geregeld? Welke software is er voor nodig en waar haal je die vandaan?

ACM heeft met behulp van Xapian/Omega een zoekmachine gemaakt voor GoT, dit omdat de SQL search niet goed genoeg preformde voor GoT, uiteindelijk is het zelfs een optie in React geworden om een extrene zoekmachine als Omega/Xapian te gebruiken.

We zijn inmiddels wel een beetje afgedwaald van het originele onderwerp van dit topic, maar jullie antwoorden zijn wel zeer bruikbaar voor me (ik hoop dat iemand anders er ook nog iets aan heeft)!

Verdre is deze FAQ ook aardig leesvoer: Omega search manual

[curry684]

ripexx schreef op vrijdag 06 mei 2005 @ 12:01:
[...]

Jep, daar komt het in pricipe wel op neer, zeker met MySQL. ik weet niet hoe Oracle en SQl server met fulltext searches omgaan maar het zou me niets verbazen als deze bij grote datasets ook preformance problemen gaan krijgen.

SQL Server leunt voor full-text search op de Windows search:

Full-text indexes are contained in full-text catalogs. Each database can contain one or more full-text catalogs. A catalog cannot belong to multiple databases and each catalog can contain full-text indexes for one or more tables. A table can only have one full-text index, so each table with a full-text index belongs to only one full-text catalog.

Full-text catalogs and indexes are not stored in the database to which they belong. The catalogs and indexes are managed separately by the Microsoft Search service.

Oftewel de Microsoft Search service (die ook voor alle HTML Help etc. wordt gebruikt) doet voor SQL Server wat Xapian voor MySQL kan doen. Enorm groot voordeel hiervan is dat in SQL Server zelfs een mixed table met Excel en Word files searchable is binnen de database (!!!). En qua performance heb je dus ook weinig te klagen

Lichtelijk offtopic natuurlijk daar het hier om MySQL handelt tho

[Verwijderd]

Bedankt, ik zal me eens in Xapian/omega gaan verdiepen! Wat mij betreft kan dit topic op slot, ik heb in ieder geval zelf niks meer te melden over dit onderwerp

[curry684]

quote: http://gathering.tweakers.net/forum/faq/forumgedrag

opgelost: Je hebt een probleemtopic geopend en het is opgelost. Post dan altijd op welke manier je het hebt opgelost. Op deze manier is het voor een volgende lezer ook makkelijker een oplossing te vinden. Post ook als je het uiteindelijk hebt 'opgelost' door middel van een herinstallatie of format. Opgelost betekent niet dat het topic daarna op slot moet.