Mysql Query in cache

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Dirk Vries

Dirk Vries

18/06/2020 10:15:59
Quote Anchor link
Ik ben bezig met een Mysql database waar veel records (producten) in staan en al deze records kunnen worden doorzocht door middel van tags (+/- 100, zoals soort, kleur, materiaal, merk) en/of losse search queries. Nu wil ik van elke tag weten hoe vaak deze voorkomen. Er moet dan ongeveer 100x een query uitgevoerd worden en de num_rows geeft een getal terug. Elke keer dat er een tag geselecteerd wordt worden de overige tags nagekeken of er nog combinaties mogelijk zijn vb:

tags:
Soort
- broek
- trui
- jas

Kleur
- rood
- wit
- blauw

Materiaal
- plastic
- katoen
- wol

Ik selecteer broek en vervolgens wordt gekeken of de kleuren rood, wit en blauw nog matchen en of de materialen nog matchen. Selecteer ik vervolgens een kleur dan wordt er weer gekeken, zo kan je verfijnen. Werkt goed, maar wat ik zeg, bij 100 queries door duizenden records is de response wat trager. Nu ben ik niet echt bekend met caching, maar las daarover dat dat een oplossing kan zijn. Wellicht een hele domme vraag, maar kan je caching ergens instellen voor je mysql database? Of heeft iemand anders een goede oplossing om dit snel te maken? Alvast bedankt.
 
PHP hulp

PHP hulp

15/07/2020 04:20:42
 
Yoop Overmaat

Yoop Overmaat

18/06/2020 11:01:25
Quote Anchor link
Het enige wat me zo in gedachten schiet is het pakket APCu-cache om het geheel is iets te versnellen.
Als ik jouw verhaal zo lees waarbij je check op check uitvoert heb ik heb het idee dat je beter de opzet van je database aan kunt passen want dit gedeelte maakt het geheel traag.
 
Rob Doemaarwat

Rob Doemaarwat

18/06/2020 11:43:42
Quote Anchor link
Dirk Vries op 18/06/2020 10:15:59:
... en de num_rows geeft een getal terug.

Als je hier letterlijk mysqli_num_rows() (of de object variant) gebruikt heb je al performance verlies. Je kunt dan beter select count(*) from ... gebruiken. In dat laatste geval komt alleen het aantal jouw kant op ("328"). Met num_rows komen die 328 records compleet jouw kant op (en als je ze dan enkel gebruikt om te tellen heb je enorm veel overhead).

Caching kun je op vele manieren realiseren (memory, disk, enz). Bij caching zit je echter altijd naar "oude data" te kijken. Hier moet je rekening mee houden. Op het hoogste niveau zal dat niet zoveel uitmaken (328 of 329 artikelen - dat maakt niet uit, in ieder geval is de combinatie mogelijk). Maar op het moment dat je een aantal tags gaat combineren worden de aantallen natuurlijk kleiner, en dan kan het gebeuren dat door het toevoegen of verwijderen van een product of tag een tag eigenlijk wel/niet getoond had moeten worden (maar omdat je met een cache werkt heb je dat nog niet verwerkt). Houd hier dus rekening mee in je caching strategie.

Sowieso is het niet handig om *alles* te cachen, want dan loopt je cache enorm uit de klauwen (gezien het enorm aantal mogelijk combinaties). Op het moment dat je op "meerdere" tags begint te zoeken zal het antwoord "direct uit de database" ook wel redelijk snel komen (er is redelijk vlot te schiften), en is het dus niet nodig om te cachen.

Hoe staat het trouwens met de indexen op je tabellen - is dat allemaal in orde? Doe eens een explain plan van je zoek-query. Misschien zijn er nog een paar "full table scans" die je kunt voorkomen door een index hier & daar toe te voegen?
Gewijzigd op 18/06/2020 13:30:02 door Rob Doemaarwat
 
Ivo P

Ivo P

19/06/2020 11:45:48
Quote Anchor link
Waarom moet dit gecached worden?

Dit moet toch ook wel real-time kunnen? Mits de data handig is opgeslagen.

Ik zou een tabel verwachten tag_product
waarbij 2 kolommen aanwezig zijn: tag_id en product_id (in unique combinatie)


Stel je wilt alle producten die tag rood (id = 99) en tag katoen (id=12) hebben

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
SELECT p.product_id, p.naam
FROM products p
JOIN tag_product tp1 ON tp1.product_id = p.product_id AND tp1.tag_id = '99'  -- rood
JOIN tag_product tp2 ON tp2.product_id = p.product_id AND tp2.tag_id = '12'  -- katoen


En dit kun je dynamisch uitbreiden met een join-regel per tag

Toevoeging op 19/06/2020 11:47:15:

en dit zou niet heel veel tijd mogen kosten. Sowieso is het maar 1 query, en niet een query in een loop.

En daarbij is wel van belang om indices en foreign key relaties toe te voegen aan je tabellen
 
Dirk Vries

Dirk Vries

19/06/2020 13:51:42
Quote Anchor link
Even ter verduidelijking, de tags staan niet in tabellen, maar worden gezocht in de text van een omschrijving. Lijkt onlogisch, maar is niet anders mogelijk. Ik heb een array van tags hardcoded in categorien ingedeeld en deze worden via een query doorgelopen:

voorbeeld:

de tags array:
kleding['type'] = "broek","trui","jas"
kleding['kleur'] = "rood","wit","blauw"
kleding['materiaal'] = "plastic","katoen","wol"
kleding['maat'] = 's','m','l'

url = search.php?tagstring=blauw,katoen,broek

systeem zoekt dan voor alle overige tags de mogelijke combinaties, dus de arry wordt doorlopen met diverse where like statements + de tag uit de array;

where text like blauw AND text like katoen AND text like broek AND text like [tag]
Zo krijg de aantallen terug die matchen, zodat je weet of er nog een match mogelijk is. Dit geef ik weer in JSON. Alle resultaten die 0 teruggeven sluit ik uit in de combinaties. zo weet ik of er van de blauw katoenen broeken nog een s of een m beschikbaar is. nogmaals, klinkt onlogisch om het zo te doen, maar is even niet anders.

Wat ik nu als oplossing ga doen is voor elke mogelijke query (die maximaal alle tags lang is) de resultaten opslaan in de db als array en deze als json terugkoppelen. Dan hoef ik de query maar 1x uit te voeren en kan ik in de database zoeken op de tagstring.
 
Ivo P

Ivo P

19/06/2020 15:44:31
Quote Anchor link
dan zou ik alsnog een tabel aanmaken met alle tags (zodat die een id krijgen).

dan zou ik een query maken die per tag zoekt of voor product X deze tag van toepassing is en if-so dat tag-id en product-id in de tag_product tabel zetten.

Dat doe je eenmalig nu met alle producten,
en je maakt 3 triggers on-insert, on-update en on-delete die dan de tag-prod. tabel weer bijwerken.
 
Dirk Vries

Dirk Vries

19/06/2020 15:51:18
Quote Anchor link
Yep, dank voor de tips, dat was ook ongeveer het plan. Zoals ik het nu heb gedaan werkt het in ieder geval al 1000x sneller. Kwam ook nog op de mogelijkheid om Redis toe te passen. Ga ik ook eens testen. In ieder geval bedankt voor het meedenken en een goed weekend
 
Dirk Vries

Dirk Vries

03/07/2020 13:54:41
Quote Anchor link
Ivo of anderen, nog even terugkomend op bovenstaande.

Ik heb 10.000 producten en 100 tags

Maak je dan van alle mogelijke combinaties een nieuwe rij aan in product_tags:
product | tag
1 | 1
1 | 3
etc

of kan je ook in de tabel product een veld aanmaken met tags en daar een array inzetten 1,3 etc
en in de product tabel andersom, een veld met een tag array.

Bij 10.000 producten en 100 tags zijn er 1.000.000 combinaties mogelijk. Lijkt me een beetje groot worden dan...
 
Ramon van Dongen

Ramon van Dongen

03/07/2020 14:30:53
Quote Anchor link
Met 1.000.000 regels in een tabel, heeft een database niet heel veel moeite mee. Mits juiste indexen etc.

Maar in een arrays op gaan slaan of meerdere gegevens in één veld, dat zorgt voor problemen.

Quote:
Bij 10.000 producten en 100 tags zijn er 1.000.000 combinaties
Niet op elk product zijn alle tags van toepassing toch?
Gewijzigd op 03/07/2020 14:31:20 door Ramon van Dongen
 
Dirk Vries

Dirk Vries

03/07/2020 14:38:16
Quote Anchor link
Okidoki, thanks. Dan gaan we dat doen.
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.