[PHP/MYSQL] Like of MATCH AGAINST zoeken

[osorkon!]

Voor een artikel zoekmachine heb ik volgende situatie:

Een gebruiker kan een zoekopdracht ingegeven via een tekstveld en daarbij gebruik maken van een 'wildcard' (in ons geval *):
Bv: *ABC*DEF*GEF*

De zoektekst wordt gefilterd zodat alleen de letters en cijfers overblijven en de * wordt vertaald naar een % teken, zodat we in onze query krijgen:

SELECT test FROM tbl_test WHERE test LIKE '%ABC%DEF%GEF%';

Het veld test in onze database is een tekst veld in een MyISAM tabel.

Dit werkt uitstekend maar naarmate onze database groeit loopt dit toch trager en trager omdat we geen gebruik kunnen maken van een index. Heb wat opzoekingswerk gedaan en kwam uit bij een FULLTEXT index op ons test veld. Nu vroeg ik me af is dit dan de ideale oplossing, en hoe zou je dit dan vertalen naar een MATCH AGAINST statement waarbij je exact dezelfde zoekresultaten krijgt? Ik zou graag de zoekmethode behouden omdat deze zeer makkelijk werkt, maar aan database kant weet ik niet of dit de ideale oplossing is. Graag jullie meningen

[FragFrog]

Afaik kun je met een asterisk ook partial matches vinden in een MATCH AGAINST query, maar lees anders ook gewoon de handleiding.

Overigens zijn er ook alternatieve search providers om tekst te vinden in een MySQL database, die schijnen vaak beter te werken

[osorkon!]

Heb ik inderdaad geprobeerd, krijg dan iets als:
SELECT test FROM tbl_test WHERE MATCH(test) AGAINST ('+*ABC* +*DEF* +*GEF*' in boolean mode);

Maar dit lijkt niet dezelfde resultaten terug te krijgen, of doe ik iets verkeerd?

[RobIII]

osorkon! schreef op maandag 20 juni 2011 @ 22:03:
Maar dit lijkt niet dezelfde resultaten terug te krijgen

Wat krijg je dan wel/niet en waarom voldoet dat niet aan je verwachting(en)?

osorkon! schreef op maandag 20 juni 2011 @ 22:03:
of doe ik iets verkeerd?

Ik vermoed dat je eens even moet kijken naar 't verschil in een LIKE en een MATCH op een Fulltext index; dat zijn namelijk nogal verschillende dingen. Zo zal een MATCH bijvoorbeeld stopwoorden ('noise words') filteren ('de', 'het', 'een', ...) en, itt bijv. MSSQL die een woordenlijst daarvoor gebruikt, zal MySQL een "woord" dat in 50% of meer van de records voorkomt tot stopwoord bombarderen (wanneer in natural language mode). Maar een beetje fulltext (ik weet er 't fijne niet van voor MySQL) zal ook rekening houden met inflections ("had", "heb", "heeft", ...) en andere zaken. Ik denk dat je dus even moet beseffen dat je een LIKE niet zomaar 1:1 kunt vervangen met een MATCH en dan dezelfde resultaten kunt verwachten.

[Voor 16% gewijzigd door RobIII op 20-06-2011 23:28]

[osorkon!]

Het lijkt alsof de AND voorwaarde (het + teken in men query) niet werkt. Ik zou dus op zen minst records moeten terug krijgen met ABC en DEF en GEF in de string, en nu is het alsof hij OF gebruikt.

Bij de LIKE krijg ik bv 200 records terug, bij de MATCH 5000 dus het verschil is groot.

SELECT test FROM tbl_test WHERE MATCH(test) AGAINST ('+*ABC* +*DEF* +*GEF*' in boolean mode);
Ik werk in boolean mode dus stopwoorden zullen niet meetellen alsook niet de 50% voorwaarde.

[Nvidiot]

Als ik het me goed herinner is de minimum woordlengte van woorden in een MySQL FULLTEXT index 4. Als je dus zoekt met LIKE 'ABC' dan zal dat niet gaan werken.

[NMe]

Nvidiot schreef op dinsdag 21 juni 2011 @ 18:25:
Als ik het me goed herinner is de minimum woordlengte van woorden in een MySQL FULLTEXT index 4. Als je dus zoekt met LIKE 'ABC' dan zal dat niet gaan werken.

Klopt inderdaad.

[osorkon!]

Maar die heb ik al verlaagd naar 3 ;-) en men tables gerepaired zodat men indexen opnieuw worden aangemaakt. Heb ook getest op alleen ABC zoeken en hij vindt de juiste records terug dan.

[Voor 66% gewijzigd door osorkon! op 21-06-2011 18:37]

[osorkon!]

Of gebruik ik meerdere statements:
SELECT * FROM tbl_test
WHERE MATCH (test) AGAINST ('*ABC*' in boolean mode)
AND MATCH (test) AGAINST ('*DEF*' in boolean mode)
...

Wat denken jullie?

[Voutloos]

Of je leest de full-text manual nog een keer: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
De * wordt enkel ondersteund aan het einde van een woord, en je kan dus full-text niet gebruiken als je op een andere locatie een wildcard nodig hebt.

Waarschijnlijk wordt '+*ABC*' geintrepreteerd als '+* ABC*', oftewel 1 verplichte term die triviaal is, en optioneel ABC* matchen.

Wellicht is het handig om je eens in te lezen over de alternatieven, wat wellicht sowieso nodig gaat zijn als je nog meer wilt tunen of performance toch tegenvalt^*, want inmiddels heb je alle opties van de ingebouwde black-box welke myisam full-text is wel gehad. Hoeven die tabellen ook niet meer per se myisam te zijn.

^*: Eigen ervaring leert dat full-text opeens onbruikbaar werd bij >1M rows. Dat punt zal vast wel iets van de setup en data afhankelijk zijn, maar de alternatieven rennen echt gewoon letterlijk rondjes om myisam full-text en daar kan je genoeg objectieve benchmarks voor vinden.

[Voor 17% gewijzigd door Voutloos op 21-06-2011 21:34]

[osorkon!]

Voutloos schreef op dinsdag 21 juni 2011 @ 21:31:
Of je leest de full-text manual nog een keer: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
De * wordt enkel ondersteund aan het einde van een woord, en je kan dus full-text niet gebruiken als je op een andere locatie een wildcard nodig hebt.

Waarschijnlijk wordt '+*ABC*' geintrepreteerd als '+* ABC*', oftewel 1 verplichte term die triviaal is, en optioneel ABC* matchen.

Wellicht is het handig om je eens in te lezen over de alternatieven, wat wellicht sowieso nodig gaat zijn als je nog meer wilt tunen of performance toch tegenvalt^*, want inmiddels heb je alle opties van de ingebouwde black-box welke myisam full-text is wel gehad. Hoeven die tabellen ook niet meer per se myisam te zijn.

^*: Eigen ervaring leert dat full-text opeens onbruikbaar werd bij >1M rows. Dat punt zal vast wel iets van de setup en data afhankelijk zijn, maar de alternatieven rennen echt gewoon letterlijk rondjes om myisam full-text en daar kan je genoeg objectieve benchmarks voor vinden.

Je hebt inderdaad gelijk, welke richting zou ik dan verder beginnen zoeken, zit nu aan 3M rows dus wordt wel nodig dat ik een alternatief vindt.

[Freeaqingme]

Een ideale oplossing gebruikt geen RDMBS, maar gebruikt sphinx, lucene/solr, etc...

[RobIII]

osorkon! schreef op dinsdag 21 juni 2011 @ 21:46:
Je hebt inderdaad gelijk, welke richting zou ik dan verder beginnen zoeken, zit nu aan 3M rows dus wordt wel nodig dat ik een alternatief vindt.

Dat wordt het tijd dat je leert googlen ([google=mysql full text alternative]) of eens creatief wordt en eens gaat kijken bij, bijvoorbeeld, wikipedia: Wikipedia: Category:Free search engine software

[Voor 23% gewijzigd door RobIII op 21-06-2011 21:50]

[osorkon!]

RobIII schreef op dinsdag 21 juni 2011 @ 21:48:
[...]

Dat wordt het tijd dat je leert googlen: [google=mysql full text alternative]

Dat doe ik ook, maar de meeste werken gelijkaardig als de MySQL fulltext search.
Dus kan het toch geen kwaad even te vragen of er mensen ervaring hebben om me de juiste richting in te duwen.

[Freeaqingme]

osorkon! schreef op dinsdag 21 juni 2011 @ 21:51:
[...]


Dat doe ik ook, maar de meeste werken gelijkaardig als de MySQL fulltext search.
Dus kan het toch geen kwaad even te vragen of er mensen ervaring hebben om me de juiste richting in te duwen.

En dan zeggen die mensen dat je beter naar iets anders kan kijken Ik zou er m'n voordeel mee doen als ik jou was

[osorkon!]

Ga me morgen even verdiepen in Sphinx en kijken hoever ik hiermee kan geraken