[MySQL] LIKE op meerdere columns en occurrences tellen

[geez]

Voor een zoekmachine probeer ik een query te schrijven die matched op meerdere columns, voor meerdere termen. Als ik bijvoorbeeld zoek op "a b", wordt elk van de columns gematched tegen "a" en "b". Om te kunnen sorteren op relevantie wil ik tellen hoeveel van de zoektermen gevonden wordt in elke row.

Ter referentie en inspiratie heb ik deze pagina gebruikt, en de query die vanuit PHP gegenereerd wordt ziet er momenteel (grofweg) als volgt uit:

MySQL:

SELECT DISTINCT COUNT(*) AS occurrences, ID
FROM tableName
WHERE (
(field1 LIKE '%a%' OR field2 LIKE '%a%') OR
(field1 LIKE '%b%' OR field2 LIKE '%b%')
)
GROUP BY ID
ORDER BY occurrences DESC, ID DESC

Het resultaat is dat hij wel matched tegen zowel "a" als "b", en tegen beiden. De waarde van occurrences blijft echter altijd 1, ongeacht het aantal van de zoektermen die in de velden voorkomt.

Iemand enig idee?

[GlowMouse]

Met SELECT IF(field1 LIKE '%a%' OR field2 LIKE '%a%', 1, 0) + IF(.... moet het lukken, maar maak alsjeblieft niet je eigen search engine.

[ Voor 3% gewijzigd door GlowMouse op 10-03-2011 20:13 ]

[Janoz]

Een idee? Ja, niet proberen op deze manier te doen. Met standaard SQL Like achtige aanpaken zul je misschien een eind komen, maar op een gegeven moment zul je toch echt tegen performance issues en andere beperkingen aanlopen.

[Verwijderd]

Als het aantal velden vast staat, en je MyISAM als engine hebt, kijk dan eens naar: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Zoeken met LIKE is nou niet echt iets waar je blij van wordt.

[Wolfboy]

Je kan het waarschijnlijk zelfs zo doen (eventueel met een integer cast erbij)

SQL:

SELECT
  (field1 LIKE '%a%') +(field2 LIKE '%a%') + (field1 LIKE '%b%') + (field2 LIKE '%b%')
...

Maar zoals de anderen al zeggen... probeer dit liever niet. Fulltext search is stukken beter hiervoor.

[TallManNL]

Moet er ook nog 2 uitkomen als field1 twee keer de a bevat? Bij mijn weten geeft Like je gewoon een true/false terug en dus geen count van het aantal keer dat a in field1 zit.

Hooguit soortgelijke query als preselectie gebruiken en erna zelf tellen, of bij grotere hoeveelheden data naar een zinnigere oplossing zoeken.

[NMe]

Je moet de counts voor a en b apart selecteren (subqueries) als je het op deze manier wil doen. Maar ik zou inderdaad ook FTS gebruiken, daar kun je ook een relevantiescore mee berekenen.

[geez]

Om iets duidelijker te zijn; Het gaat om een zeer kleine database, dus van prestatieproblemen zal ik geen last hebben. Neemt niet weg dat efficient programmeren natuurlijk de voorkeur heeft. Het is overigens een InnoDB database, voornamelijk omdat ik veel gebruik maak van foreign keys.

Fulltext search wordt dus lastig, en voor zover ik weet heeft InnoDB geen gelijkwaardige functionaliteit.

[ Voor 12% gewijzigd door geez op 14-03-2011 09:55 ]

[TallManNL]

Blijft mijn vraag toch nog staan, als je field1 de waarde 'haha' heeft, moet er dan bij de telling voor a 1 of 2 uitkomen?

[NMe]

geez schreef op maandag 14 maart 2011 @ 09:53:
Om iets duidelijker te zijn; Het gaat om een zeer kleine database, dus van prestatieproblemen zal ik geen last hebben. Neemt niet weg dat efficient programmeren natuurlijk de voorkeur heeft. Het is overigens een InnoDB database, voornamelijk omdat ik veel gebruik maak van foreign keys.

Fulltext search wordt dus lastig, en voor zover ik weet heeft InnoDB geen gelijkwaardige functionaliteit.

Je hoeft niet elke tabel in MyISAM te zetten, alleen die tabellen waar je een FT-index op wil zetten. Ik zou eerder daarvoor kiezen dan voor een houtje-touwtje oplossing waarbij nogal wat valkuilen te bedenken zijn. Voor elke nieuwe zoekterm moet je een nieuwe subquery toevoegen en een nieuwe LIKE toevoegen. En dan heb ik het nog niet eens over de vraag van TallManNL hierboven; als je records waarin dezelfde tekst vaker dan één keer voorkomt een hogere score wil geven, dan wordt je probleem ineens een stuk lastiger zonder FTS.

[geez]

@TallManNL: In eerste instantie ging het erom dat het aantal van de keywords dat in het item voorkwam geteld werd, omdat het een redelijke veronderstelling is dat items waar alle keywords in voorkomen relevanter zijn dan maar een aantal. Echter of een keyword 1 of 10 keer genoemd wordt, kan inderdaad ook belangrijk zijn. Het lijkt me dan ook inderdaad wenselijk te tellen hoe vaak een keyword gevonden wordt.

Inmiddels wat meer gezocht op hoe men de wens van fulltext searches i.c.m. InnoDB tables heeft afgehandeld. Het lijkt er veelal op neer te komen dat men of de table converteert naar MyISAM, dan wel een realtime/periodieke kopie maakt van de table om te gebruiken als search table. Ik neig momenteel naar het laatste i.v.m. foreign keys, maar ik overweeg ook om geen fulltext search te gebruiken en niet te sorteren op relevantie, omdat de dataset zeer klein is en de winst bij dergelijke sortering dus gering (het moet wel het werk waard blijven ). Allen dank voor de input!

[ Voor 4% gewijzigd door geez op 14-03-2011 20:45 ]

[NMe]

Realtime een tabel "even" naar MyISAM converteren alleen om een FTS te kunnen doen?

Die foreign key constraints zijn leuk maar je zit het je nu echt onnodig moeilijk te maken. Die FK-restraints op één tabel laten varen is vervelend maar stiekem is het veel vervelender om eromheen te moeten gaan hacken.

[geez]

Realtime, daar was ik ook geen fan van nee Ik ga afwegen of het aantrekkelijker is om over te gaan naar MyISAM en de nodige foreign key constraints bij PHP onder te brengen, of relevantiesortering te laten varen en de huidige - reeds werkende - oplossing te gebruiken.

[Voutloos]

NMe schreef op maandag 14 maart 2011 @ 20:55:
Die FK-restraints op één tabel laten varen is vervelend maar stiekem is het veel vervelender om eromheen te moeten gaan hacken.

Geen support voor transactions kan nog vervelender zijn.