Toon posts:

[php/sql] Forum doorzoeken -> Permissies

Pagina: 1
Acties:

Onderwerpen


  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
Hey,

Mijn forum bevat delen (categorieën) die alleen voor VIP leden en / of crewleden beschikbaar zijn. Tijdens het doorzoeken van het forum moet ik dus controleren of diegene die zoekt toegang heeft tot bepaalde topics.

Mijn database opzet is als volgt:

- forum_cats
- forum_subcats
- forum_topics
- forum_posts

(ik heb categorieën en subcategorieën apart gehouden, ik weet dat het anders kan, maar dat kan ik nu helaas niet meer 1 2 3 veranderen)

Bij het doorzoeken wordt zowel forum_topics als forum_posts doorzocht op titel, bericht, gebruikersnaam, etc.

Nu moet er dus gecontroleerd worden of de gevonden topics zich in legitieme categorieën bevinden (lees: categorieën waar de gebruiker toegang tot heeft).

Nu kan ik dit in een heel ingewikkelde query gieten, zoiets als:

SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SELECT 
                    MAX(forum_posts.id) AS postid,
                
                    DATE_FORMAT(MAX(forum_posts.datum), '%d-%m-%Y om %H:%i uur') AS postdatum,
                    UNIX_TIMESTAMP(MAX(forum_posts.datum)) AS postdatumunix,
                    
                    MAX(forum_topics.id) AS topicid,
                    MAX(forum_topics.titel) AS topictitel,
                    MAX(forum_topics.sticky) AS topicsticky,
                    MAX(forum_topics.gesloten) AS topicgesloten,
                    
                    ((SELECT COUNT(*) FROM forum_posts WHERE topicid = MAX(forum_topics.id)) - 1) AS reactieaantal,
                    
                    MAX(leden.id) AS topicstarterid,
                    MAX(leden.gebruikersnaam) AS topicstartergebruikersnaam

                FROM 
                    forum_posts
                INNER JOIN
                    forum_topics
                ON
                    forum_topics.id = forum_posts.topicid
                INNER JOIN
                    forum_subcats
                ON
                    forum_subcats.id = forum_topics.subcat
                INNER JOIN
                    forum_cats
                ON
                    forum_cats.id = forum_subcats.cat
                LEFT JOIN
                    leden
                ON
                    leden.id = forum_topics.starter
                LEFT JOIN
                    leden AS ledenposter
                ON
                    ledenposter.id = forum_posts.lidid
                WHERE 
                    ledenposter.gebruikersnaam = 'keyword'
                AND
                    forum_cats.id IN (1,2,3,4,5,6,7,8,9,10,11,18,19)
                GROUP BY
                    forum_posts.topicid


Bovenstaande query is zo traag als ... (5 joins lijkt mij ook niet echt geschikt), dus dit is geen optie (dit is ook maar een (hele slechte) opzet).

Hebben jullie ideeën hoe ik deze bewerking gemakkelijk kan uitvoeren? Ik had zelf in gedachte om eerst "normaal" te zoeken door alle topics en posts met een (simpele) query en met php de topics er uit filteren. Echter is dit ook zeer omslachtig, aangezien je dan met een foreach loop door alle resultaten moet wandelen en de betreffende topics uit de array filteren.

  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
Noork schreef op maandag 11 oktober 2010 @ 20:44:
Je kunt ook e.e.a. de-normaliseren. Dus extra attributen opnemen bij de post en het topic bijvoorbeeld.
Maar wat precies (niet om lui over te komen hoor)? Want de permissies kunnen ook nog wel eens veranderen. En allemaal extra insert en update queries uitvoeren (om die extra gede-normaliseerde gegevens te updaten) bij het toevoegen, wijzigen en verwijderen van berichten vind ik persoonlijk ook geen sierlijke oplossing.

[Voor 5% gewijzigd door radem205 op 11-10-2010 20:50]


  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
Noork schreef op maandag 11 oktober 2010 @ 20:59:
Is het trouwens zonder al die inner joins wel snel; afgezien de permissies? Heb je wel indexes op je tabellen? En wellicht is een gewone query en database hier helemaal niet zo geschikt voor. Op Got maken ze ook geen gebruik van gewone mysql statments, maar van Xapian.
Zonder de inner joins is ie wel snel (ook zonder de inner joins op forum_cats en forum_subcats is ie snel). En met EXPLAIN is te zien dat er goed gebruik gemaakt wordt van indexes.

Edit: Hmm, nu doet ie er 0.007 seconde over om 18000 berichten te doorzoeken met de, aan de hand van de suggesties van Rob, aangepaste query.

@RobII: Bedankt voor je uitgebreide reactie. De Group by klopt niet doordat ik forum_posts.topicid niet in de SELECT heb staan zeker, want voor de rest klopt ie volgens mij wel, toch? En hoe denk jij de subquery te vereenvoudigen (of te vervangen) dan?
Ik zie ook even niet waarom je een LEFT JOIN op leden doet? En die tweede LEFT JOIN zie ik ook niet helemaal. Een topic heeft toch altijd een topicstarter? En die tweede (ledenposter) zie ik even niet van waar die voor dient?
Die LEFT JOIN is er om ook te kunnen zoeken op de gebruikersnaam van een poster. Daarvoor is het lijkt mij noodzakelijk om een JOIN op de ledentabel te doen om de gebruikersnaam te achterhalen, toch? Of gaat het je met name om de LEFT in plaats van een INNER? Dat is inderdaad waar.

De 2e LEFT JOIN is een LEFT JOIN (en geen INNER) omdat het kan zijn dat bepaalde posts geen poster meer hebben omdat het kan zijn dat een account is verwijderd. Hierdoor zullen die berichten wel meegenomen worden om het betreffende bericht te doorzoeken.

[Voor 54% gewijzigd door radem205 op 11-10-2010 21:24]


  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
Ik heb de WHERE forum_cats.id IN (..... naar de JOIN voorwaarde gehaald. Van de ene LEFT JOIN heb ik een INNER JOIN gemaakt.
RobIII schreef op maandag 11 oktober 2010 @ 21:23:

[...]

Ok; dat kan dan. Maar je verwijdert een poster dus ook echt? Zou je 'm niet beter gewoon soft-deleten? Los van wat DB-technisch nou handig/goed zou zijn: een post zonder poster is natuurlijk maar raar/onmogelijk feitelijk.
Dit is helaas niet meer eenvoudig te veranderen en de eigenaar wilde om privacy redenen de accounts ook echt verwijderd hebben. Dus wanneer de leden de accounts verwijderen moeten de gegevens ook echt verwijderd zijn (op zich privacy-technisch goed vind ik zelf).

Volgens mij is 0.007 seconde wel acceptabel of kan het nog veel sneller denk je?

En nog even wat anders: Wanneer ik SQL_CALC_FOUND_ROWS gebruik in de query (wat ik overigens zeer zelden gebruik) dan wordt ie aanzienlijk langzamer. Ik heb ook ergens gelezen dat SQL_CALC_FOUND_ROWS de boel aanzienlijk vertraagd en je beter een aparte query kan gebruiken.
Weet jij of dit correct is (of dat jij andere ervaringen hebt)?

Edit: Lees net je toevoeging. Het was inderdaad beter om te anonimiseren ja, maar het is helaas niet meer terug te draaien (althans niet gemakkelijk). Dus jullie gebruiken "FUBAR" als gebruikersnaam voor diegene die zijn / haar account heeft "verwijderd"?

[Voor 23% gewijzigd door radem205 op 11-10-2010 21:32]


  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
dirkpostma schreef op maandag 11 oktober 2010 @ 21:32:
[...]


...is enorm te optimaliseren. Je kunt t.b.v. het tellen van posts in een topic en topics in een forum veel beter het aantal berichten opslaan in een apart veldje. Reden: dit het aantal posts wijzigt niet zo verschrikkelijk vaak (vergeleken met het aantal read queries) en het kost best veel tijd om dit telkens opnieuw te berekenen.

Het nadeel is dat je dit veldje als programmeur zelf goed moet bijhouden. Dwz. bij elke create post of delete post dient ook dit veldje een update te krijgen en eventueel een "recount all" method als je db corrupt is geworden o.i.d..

Bij reguliere fora levert dit giga-tijdswinst op, dus is zeker de moeite waard.
Ik dacht dat zo'n simpele COUNT wel aardig snel blijft (ongeacht de grootte van de tabel). De query heeft maar één WHERE clause op een geindexeerde kolom, dat moet toch vrij snel gaan lijkt mij zo?
dirkpostma schreef op maandag 11 oktober 2010 @ 21:32:
[...]

Edit: en als het even kan zou ik ook het anonimiseren doorvoeren. Je queries worden sneller uitgevoerd, je code wordt simpeler => win win.
Bij welke queries geeft dat een tijdswinst op dan? Want tussen een LEFT JOIN of INNER JOIN zit toch niet heel veel tijdsverschil?

[Voor 17% gewijzigd door radem205 op 11-10-2010 21:37]


  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
dirkpostma schreef op maandag 11 oktober 2010 @ 21:36:
Bij 1 query is weliswaar snel, maar als je 18000 resultaten hebt, wordt die query 18000 keer uitgevoerd. Misschien is het geen giga-optimalisatie, maar het scheelt wel, bij een grote reads/writes verhouding.
Op de zoekquery (de query in de topicstart) zit normaliter een limiet van 20 items, dus zal hooguit 20 keer de count per aanvraag worden uitgevoerd. Dit lijkt mij te overzien.
Edit: nu ik erover nadenk zou het kunnen dat de db zo slim is om het alleen maar voor je opgegeven limit keer uit te voeren, maar zeker ben ik daarvan niet. Je zou het kunnen testen door dat stukje er even uit te halen.
Het lijkt mij inderdaad het geval, aangezien het aantal items (dus ook de query) binnen je SELECT afhankelijk is van je LIMIT.

[Voor 27% gewijzigd door radem205 op 11-10-2010 21:41]


  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
dirkpostma schreef op maandag 11 oktober 2010 @ 21:40:
Je hebt geen limit in je openingspost staan ;-)
Nee dat klopt, daarom vermeld ik het even ;)

Maar wanneer levert het anonimiseren volgens jou een tijdswinst op, want die zie ik nog niet helemaal...

Edit: En hoe moet ik deze zin (Rob) precies lezen: "de discussie is fubar zonder poster" (want ik kan er weinig van bakken, het is al laat :p)

[Voor 38% gewijzigd door radem205 op 11-10-2010 21:47]


  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
RobIII schreef op maandag 11 oktober 2010 @ 22:04:
[...]

Wikipedia: FUBAR ;)
Gewoon anonimiseren. Voor de rest; sorry. Ben even druk druk druk
Thanks, maakt niet uit joh. Je helpt de mensen hier altijd goed, en ik ben allang blij dat je me wilt helpen :)

  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
Gomez12 schreef op maandag 11 oktober 2010 @ 22:34:
Waar komen eigenlijk de cats_id vandaan? Ik zie hier een leuk lijstje (1,2,3,4,5,6,7,8,9,10) staan.
Maar ik neem aan dat die ook gewoon in de dbase staan, waarom is dit geen extra join?
Die id's haal ik eerst met een query uit de database om vervolgens te kunnen gebruiken in de bediscussieerde query. Hoe kan je dit vervangen door een join dan (want dan moet in die join gekeken worden of er permissie is om een topic te kunnen openen in de betreffende categorie?

En op welke manier kan je het beste anonimiseren? Want FUBAR is naar mijn weten alleen een bepaalde benaming voor gegevens die je niet meer wilt gebruiken, maar nog wel behouden blijft.

Word hier op Got de gebruikersnaam van een verwijderd lid vervangen door een standaard gebruikersnaam?

  • radem205
  • Registratie: juni 2002
  • Laatst online: 20-09-2020
Gomez12 schreef op maandag 11 oktober 2010 @ 22:52:
[...]

Zolang er maar een verhaaltje/denkwijze achter zit vind ik het meeste wel best :)

Enkel kan dit op den duur onverwacht veel tijd gaan kosten. Afaik kan menig query-optimizer nog steeds beroerd omgaan met handmatige IN-values. Met 10 / 20 waardes kost het waarschijnlijk niet je kop, maar het is even iets om op te letten voordat je 200/1000 categorieeen hebt :)
Maar ik zal niet weten hoe je dit in een JOIN kan vervangen, kan jij een voorbeeldje geven wellicht?
Pagina: 1


Nintendo Switch (OLED model) Apple iPhone 13 LG G1 Google Pixel 6 Call of Duty: Vanguard Samsung Galaxy S21 5G Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True

Tweakers maakt gebruik van cookies

Bij het bezoeken van het forum plaatst Tweakers alleen functionele en analytische cookies voor optimalisatie en analyse om de website-ervaring te verbeteren. Op het forum worden geen trackingcookies geplaatst. Voor het bekijken van video's en grafieken van derden vragen we je toestemming, we gebruiken daarvoor externe tooling die mogelijk cookies kunnen plaatsen.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Forum cookie-instellingen

Bekijk de onderstaande instellingen en maak je keuze. Meer informatie vind je in ons cookiebeleid.

Functionele en analytische cookies

Deze cookies helpen de website zijn functies uit te voeren en zijn verplicht. Meer details

janee

    Cookies van derden

    Deze cookies kunnen geplaatst worden door derde partijen via ingesloten content en om de gebruikerservaring van de website te verbeteren. Meer details

    janee