[ASP.NET Core / MySQL] Forum zoekfunctie met paging

[NickThissen]

Ik heb een "soort van" forum gebouwd op ASP.NET Core met een MySQL database en ben nu bezig om de (momenteel extreem simpele) zoekfunctie iets uit te breiden.

De database structuur is ongeveer als volgt (alleen de relevante details)

Post (Id, ThreadId, Contents, CreatedTime)
Thread (Id, ForumId, Title)
Forum (Id, Title)

Zoals gewend dus een parent Forum tabel, met daarin meerdere Threads, en in elke thread weer meerdere Posts.


De huidige zoekfunctie komt neer op niks meer dan een select * from posts where contents like "%zoekterm%". Dit werkt OK maar is niet wat ik wil.

Ik wil minimaal het volgende:
- Mogelijkheid om meerdere zoektermen apart te zoeken (dus niet als een zin, maar elk woord apart)
- Sorteren op "relevantie", dwz resultaten waarin de meeste zoektermen voorkomen komen voor resultaten waarin er slechts 1 voorkomt.
- Of nog dieper: zoeken op resultaten die "ongeveer" matchen (fuzzy search dmv Levenstein distance oid).
- Paging (niet 30,000 resultaten op een pagina).


Hierdoor loop ik tegen een aantal problemen op:

1. Ik zou niet weten hoe ik de logica voor "zoeken naar meerdere termen ipv de hele zin", of nog erger de logica van fuzzy search, in een MySQL query zou moeten bouwen. Kan dat uberhaupt? Het lijkt me sterk. Het enige alternatief dat ik zie is om eerst ALLE posts op te halen, en ze daarna "aan de C# kant" te gaan filteren. Is dit hoe een search normaal werkt? Dit zal toch heel traag gaan worden voor groot aantal posts? In veel gevallen zal er misschien maar 1 post matchen, om dan een heel forum van honderdduizend posts te gaan ophalen lijkt niet handig...

2. Vergelijkbaar: hoe implementeer ik op de juiste manier paging indien ik eerst alle posts moet ophalen? Ik kan dus niet een "LIMIT" naar de database sturen omdat ik na de database query pas ga kijken hoeveel matches ik heb. Het resultaat is dat ik dus voor elke pagina opnieuw weer alle posts moet ophalen en moet filteren, om er daarna weer tig weg te gooien en een subset te laten zien.

Dit kan toch niet de bedoeling zijn?

Wat mis ik hier?

[The Eagle]

Een search heeft meestal een crawler / indexer die op de achtergrond opgebouwd cq aangevuld wordt. Zie het als een soort indexen of losse tabellen die je benaderd. Alleen weet jij niet vooraf wat de zoektermen zijn, dus wil je alles indexen.

Als je zoiets al op wilt lossen zou ik een echte searchtool als Elasticsearh of Solr of inzetten. Laat maar lekker indexen en als je iets op moet halen kijk je in de search, niet rechtstreeks op je DB. Dat laatste is namelijk vanaf een DB perspectief gezien heel duur, legt een (onnodig) grote last op je DB IO, vertraagt de boel, etc.

[DJMaze]

Jij bent op zoek naar https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

[NickThissen]

DJMaze schreef op dinsdag 9 januari 2018 @ 20:01:
Jij bent op zoek naar https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

Dat lijkt inderdaad precies wat ik nodig heb, blijkbaar kan het wel in MySQL zelf Vreemd dat ik dit zelf niet gevonden heb.... Anyway ik ga er mee aan de slag, bedankt!

[RobIII]

NickThissen schreef op dinsdag 9 januari 2018 @ 19:32:
2. Vergelijkbaar: hoe implementeer ik op de juiste manier paging indien ik eerst alle posts moet ophalen? Ik kan dus niet een "LIMIT" naar de database sturen omdat ik na de database query pas ga kijken hoeveel matches ik heb. Het resultaat is dat ik dus voor elke pagina opnieuw weer alle posts moet ophalen en moet filteren, om er daarna weer tig weg te gooien en een subset te laten zien.

SQL_CALC_FOUND_ROWS


Maar ook al gebruik je dat niet; je zou dan altijd eerst een:

SQL:

select count(1) from ... where ... 

doen om het totaal aantal rijen te bepalen en daarna een

SQL:

select foo, bar, baz from ... where ... limit y, z

om daarmee "pagina X" op te halen; er is dan niet echt sprake van "eerst alle records ophalen, die dan allemaal weggooien en een subset ophalen". Je haalt de count op (en dus hoeven niet alle gegevens van alle records over de lijn bijv.) en daarna alleen de subset die je zocht. Ja, je DB gaat wel (in essentie, caching en andere slimmigheden daargelaten) 2 keer de "query" (vooral de "where clause") uitvoeren, maar daar zijn RDBMS'en nou juist slim in en dat is juist waar queryplans, executionplans, caching, statistics en die slimmigheden allemaal voor uitgevonden zijn. Maar dan is 't dus wel zaak dat je een count gebruikt in de ene query i.p.v. dat je de select uitvoert, in PHP de rijen gaat tellen en dan nog eens de query uitvoert met de limit (en, ja, ik heb het écht (meer dan eens ) zien gebeuren...).

Persoonlijk vind ik, in het geval van MySQL, de SQL_CALC_FOUND_ROWS wel mooi. Een enkele roundtrip naar de DB; de efficiency is (in mijn ervaring) prima maar er wordt nog wel eens anders beweerd. Kwestie van benchmarken voor jou specifieke geval(len) en een methode kiezen

Voor vraag 1 heb je al twee (goede) antwoorden

[ Voor 54% gewijzigd door RobIII op 09-01-2018 21:56 ]

[NickThissen]

RobIII schreef op dinsdag 9 januari 2018 @ 21:31:
[...]


SQL_CALC_FOUND_ROWS


Maar ook al gebruik je dat niet; je zou dan altijd eerst een:

SQL:

select count(1) from ... where ... 

doen om het totaal aantal rijen te bepalen en daarna een

SQL:

select foo, bar, baz from ... where ... limit y, z

om daarmee "pagina X" op te halen; er is dan niet echt sprake van "eerst alle records ophalen, die dan allemaal weggooien en een subset ophalen".

Het punt was juist dat ik in de veronderstelling was dat ik al mijn zoeken en filteren niet op de DB zou kunnen doen maar enkel in mijn "server side code" (C# of php of whatever). Blijkbaar klopt dat niet en heeft MySQL wel degelijk een aantal slimme zoekfuncties. Maar als dit niet het geval zou zijn dan zou ik dus wel eerst alle rijen moeten ophalen, om ze daarna allemaal in de code te gaan checken voor een match (of een ingewikkelde 'fuzzy search' logic toepassen). Pas nadat ik dit gedaan heb weet ik hoeveel matches ik heb, en dus hoeveel pagina's ik moet tonen.

Als ik alle zoek en filter dingen in de query zelf kan doen, dan is er geen probleem want dan kan ik inderdaad met een query het aantal matches opvragen en weet ik meteen hoeveel pagina's er zullen zijn.

Nog geen tijd gehad om het te proberen maar ik denk dat ik er zo wel uit kom

[DJMaze]

NickThissen schreef op woensdag 10 januari 2018 @ 15:42:
Nog geen tijd gehad om het te proberen maar ik denk dat ik er zo wel uit kom

Niet op een kleine dataset

Woorden moeten standaard minimaal 4 tekens bevatten (ft_min_word_len) en er is een maximum (ft_max_word_len).
En dan heb je nog de innodb_ft_min_token_size en innodb_ft_max_token_size.
Maar als je zoekt met ngram wordt daar geen rekening mee gehouden.
Anders worden ook woorden die in meer dan 50% van de records voorkomen ook genegeerd.

Maar goed, je kan doen als google: tweakers mysql -jquery "fuzzy search"

offtopic:
"-jquery" gebruik ik overal. wordt krankjorum van alle zoekresultaten waarbij iedereen alles met dat wil oplossen

[ Voor 14% gewijzigd door DJMaze op 11-01-2018 00:20 ]

[NickThissen]

DJMaze schreef op donderdag 11 januari 2018 @ 00:15:
[...]

Niet op een kleine dataset

Woorden moeten standaard minimaal 4 tekens bevatten (ft_min_word_len) en er is een maximum (ft_max_word_len).
En dan heb je nog de innodb_ft_min_token_size en innodb_ft_max_token_size.
Maar als je zoekt met ngram wordt daar geen rekening mee gehouden.
Anders worden ook woorden die in meer dan 50% van de records voorkomen ook genegeerd.

Maar goed, je kan doen als google: tweakers mysql -jquery "fuzzy search"

offtopic:
"-jquery" gebruik ik overal. wordt krankjorum van alle zoekresultaten waarbij iedereen alles met dat wil oplossen

Sorry het is me even helemaal niet duidelijk wat je bedoelt?

[Down]

2. Vergelijkbaar: hoe implementeer ik op de juiste manier paging indien ik eerst alle posts moet ophalen? Ik kan dus niet een "LIMIT" naar de database sturen omdat ik na de database query pas ga kijken hoeveel matches ik heb. Het resultaat is dat ik dus voor elke pagina opnieuw weer alle posts moet ophalen en moet filteren, om er daarna weer tig weg te gooien en een subset te laten zien.

Als je Entity Framework Core gebruikt kun je wellicht de MySQL Provider gebruiken (die overigens zo te zien pre release is). Ik heb zelf alleen ervaring met MSSQL in dit verband, maar paging is dan met Linq vrij éénvoudig:

Arbitrair voorbeeld:

C#:

var posts = await context.Posts.Skip(5).Take(10).ToListAsync();

Je zou dan wel even moeten kijken of dit wordt vertaald naar SQL die je aanstaat .

EDIT: andere mensen hebben ook succes met third party providers zo te zien.

[ Voor 7% gewijzigd door Down op 11-01-2018 23:13 ]

[DJMaze]

NickThissen schreef op donderdag 11 januari 2018 @ 21:37:
Sorry het is me even helemaal niet duidelijk wat je bedoelt?

Je hoeft niet te quoten, jouw bericht staat direct onder die van mij.
Wat je niet begrijpt kan je Googlelen.

[hellum]

Ik ga er eigenlijk vanuit dat met SELECT COUNT en LIMIT al voldoende zijn, maar je zou ook nog Sphinxsearch als storage engine in MySQL kunnen installeren. Dat is er voor gemaakt (neemt ook stemming ed. mee).

[NickThissen]

Down schreef op donderdag 11 januari 2018 @ 23:09:
[...]


Als je Entity Framework Core gebruikt kun je wellicht de MySQL Provider gebruiken (die overigens zo te zien pre release is). Ik heb zelf alleen ervaring met MSSQL in dit verband, maar paging is dan met Linq vrij éénvoudig:

Arbitrair voorbeeld:

C#:

var posts = await context.Posts.Skip(5).Take(10).ToListAsync();

Je zou dan wel even moeten kijken of dit wordt vertaald naar SQL die je aanstaat .

EDIT: andere mensen hebben ook succes met third party providers zo te zien.

Ik gebruik inderdaad EF Core met een third party provider.

Helaas is de "MATCH" functie niet ingebouwd en moet ik dus zelf de SQL gaan opstellen en die handmatig insturen via de "FromSql" functie. Dat is in principe geen probleem, echter lijkt de FromSql functie niet goed te werken... Zolang ik een enkele SELECT doe zonder JOINs dan werkt het prima en kan ik de MATCH gebruiken om te zoeken. Ik wil echter ook graag een JOIN zodat ik voor elke Post ook de Author kan meenemen. Zodra ik ook maar iets van een JOIN ga doen dan crasht hij ergens in de EF code met een "Sequence contains more than one element" (hij doet ergens een SingleOrDefault op een collectie met meer dan een item denk ik). Ik kom er nog niet helemaal uit waar het probleem zit (mijn model? EF? De third party provider? MySQL is het niet want dezelfde query in de Workbench werkt prima).

Voor nu kan ik ermee weg door de Authors achteraf op de halen met een nieuwe query. Niet heel netjes maar aangezien ik toch maar 10 resultaten per pagina laat zien valt het allemaal wel mee.

Met deze "hack" heb ik het aan de praat, bedankt


Verder twijfel ik nog tussen de 'natural language' en 'boolean' modes. Natural language lijkt betere "relevance" te berekenen waardoor "sort by relevance" goed werkt; bij boolean lijkt alles een relevance van 1 of 0 te krijgen waardoor de sortering min of meer random lijkt. Echter doet natural language mode wat vreemde dingen zoals (zoals al gezegd) resultaten negeren als de zoekterm in meer dan 50% van de rows terug komt. Dat werkt later als de database gevuld is vast goed, maar op het moment met weinig data is dat heel vreemd.

Goed, ik bouw wel een toggle in, nu nog even uitzoeken hoe ik dat voor de gebruikers duidelijk maak

DJMaze schreef op vrijdag 12 januari 2018 @ 00:16:
[...]

Je hoeft niet te quoten, jouw bericht staat direct onder die van mij.
Wat je niet begrijpt kan je Googlelen.

Het waren niet de termen die je gebruikt dat ik niet begreep, ik begreep het punt van je post als geheel niet. Achteraf denk ik dat je bedoelt dat testen op een kleine database niet lekker werkt door die 50% regel? In dat geval snap ik het maar dat kwam niet direct over