[php/sql] Forum doorzoeken -> Permissies

[radem205]

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:

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.

[Noork]

Je kunt ook e.e.a. de-normaliseren. Dus extra attributen opnemen bij de post en het topic bijvoorbeeld.

[radem205]

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]

[Noork]

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.

[RobIII]

radem205 schreef op maandag 11 oktober 2010 @ 20:40:
Nu kan ik dit in een heel ingewikkelde query gieten

Met alle respect; maar hier is niets ingewikkelds aan. Sure, het is meer dan de gemiddelde "select * from mytable" die je in een willekeurige PHP tutorial tegen komt maar voor een beetje RDBMS is dit peanuts.

radem205 schreef op maandag 11 oktober 2010 @ 20:40:
Bovenstaande query is zo traag als ...

Al eens gekeken/gezocht naar de oorzaak? Meten = weten. Doe eens een explain bijvoorbeeld.

radem205 schreef op maandag 11 oktober 2010 @ 20:40:
(5 joins lijkt mij ook niet echt geschikt)

Ik ken queries met nog veel meer joins op een shitload aan data die performen als een tiet. Het aantal joins zegt helemaal niet zolang we niet meer weten over indexen, hoeveelheid data, welk RDBMS, hardware etc. etc. Even aangenomen dat 't over een gemiddeld forumpje gaat kun je er van uit gaan dat je ergens in je explain wel gaat vinden wat de oorzaak is of je draait je RDBMS op een 286

radem205 schreef op maandag 11 oktober 2010 @ 20:40:
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.

Wat je zou kunnen proberen is de WHERE verhuizen naar de join; dat kan soms wel schelen omdat er een aantal zaken al eerder weggesneden kunnen worden uit de resultset bij een beetje matige query planner.

SQL:

SELECT ...
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
                AND forum_cats.id IN (1,2,3,4,5,6,7,8,9,10,11,18,19)
LEFT JOIN leden ON leden.id = forum_topics.starter
LEFT JOIN leden AS ledenposter ON ledenposter.id = forum_posts.lidid
WHERE ledenposter.gebruikersnaam = 'keyword'
GROUP BY ...

/edit: Oh, ik zie nu je subquery pas Ik vermoed dat dat ook wel anders kan, en 't lijkt me (zonder me verder te verdiepen) de meest waarschijnlijke oorzaak van je probleem

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?

Noork schreef op maandag 11 oktober 2010 @ 20:59:
Op Got maken ze ook geen gebruik van gewone mysql statments, maar van Xapian.

Dat is voor het "full text search" deel AFAIK, niet omdat een paar joins zo zwaar zouden zijn.

[Voor 12% gewijzigd door RobIII op 11-10-2010 21:07]

[radem205]

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]

[RobIII]

radem205 schreef op maandag 11 oktober 2010 @ 21:04:
Edit: Hmm, nu doet ie er 0.07 seconde over om 18000 berichten te doorzoeken met bovenstaande query.

Wat heb je veranderd?

radem205 schreef op maandag 11 oktober 2010 @ 21:04:
@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?

Ik keek er even langs; die opmerking was niet terecht. Ignore maar

radem205 schreef op maandag 11 oktober 2010 @ 21:04:
En hoe denk jij de subquery te vereenvoudigen (of te vervangen) dan?

Weet ik even niet; ik zit even met teveel zaken aan m'n hoofd op 't moment om me in je query te verdiepen. Sorry. Maar 't kan vast

radem205 schreef op maandag 11 oktober 2010 @ 21:04:
Of gaat het je met name om de LEFT in plaats van een INNER?

Inderdaad.

radem205 schreef op maandag 11 oktober 2010 @ 21:04:
Hierdoor zullen die berichten wel meegenomen worden om het betreffende bericht te doorzoeken.

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. Behalve dat je dus een thread daarmee om zeep helpt (geen poster(s) meer) maak je het jezelf daarmee ook niet echt makkelijk dus. Dat is ook (1 van de, uiteraard) reden(en) waarom wij geen accounts verwijderen maar 'anonimiseren' als iemand weg wil op GoT; de discussie is fubar zonder poster.

[Voor 8% gewijzigd door RobIII op 11-10-2010 21:25]

[radem205]

RobIII schreef op maandag 11 oktober 2010 @ 21:23:
[...]

Wat heb je veranderd?

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]

[dirkpostma]

SELECT COUNT(*) FROM forum_posts WHERE topicid = MAX(forum_topics.id)) - 1) AS reactieaantal

...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.

Edit: en als het even kan zou ik ook het anonimiseren doorvoeren. Je queries worden sneller uitgevoerd, je code wordt simpeler => win win.

[Voor 8% gewijzigd door dirkpostma op 11-10-2010 21:35]

[radem205]

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]

[dirkpostma]

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.

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.

[Voor 72% gewijzigd door dirkpostma op 11-10-2010 21:39]

[radem205]

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]

[dirkpostma]

Je hebt geen limit in je openingspost staan ;-)

[radem205]

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 )

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

[RobIII]

radem205 schreef op maandag 11 oktober 2010 @ 21:41:
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 )

Wikipedia: FUBAR
Gewoon anonimiseren. Voor de rest; sorry. Ben even druk druk druk

[Voor 23% gewijzigd door RobIII op 11-10-2010 22:04]

[radem205]

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

[Cartman!]

Wat hij bedoelt is dat je bij het verwijderen van een user de user niet als record zijnde verwijdert maar alleen zijn gegevens. Zo blijft het record van de user in je tabel staan en blijft ieder topic een topicstarter houden. Dat kan je een hoop ellende besparen en het is voor de user ook wat duidelijker waarschijnlijk

[Gomez12]

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?

[RobIII]

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?

Dat lijkt me (op de gok) iets dat de businesslogic in de query flempt. Dat zou overigens ook inderdaad met een join (moeten) kunnen, maar dat is wat ik gok dus

[radem205]

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?

[Gomez12]

RobIII schreef op maandag 11 oktober 2010 @ 22:40:
[...]

Dat lijkt me (op de gok) iets dat de businesslogic in de query flempt. Dat zou overigens ook inderdaad met een join (moeten) kunnen, maar dat is wat ik gok dus

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

[radem205]

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?

[Gomez12]

radem205 schreef op maandag 11 oktober 2010 @ 22:58:
[...]


Maar ik zal niet weten hoe je dit in een JOIN kan vervangen, kan jij een voorbeeldje geven wellicht?

inner join op een tabel met user-id en group-id en dan
inner join op een tabel met group-id en toegestane cat-nrs? ( Geen idee hoe je permissies precies werken, maar zolang je die niet komma gescheiden in 1 veld gedumpt hebt zou het iets vergelijkbaars moeten zijn )

zijn 2 whopping joins meer, maar schaalt voor je query-optimizer vele malen beter...

[dirkpostma]

radem205 schreef op maandag 11 oktober 2010 @ 21:41:
[...]Maar wanneer levert het anonimiseren volgens jou een tijdswinst op, want die zie ik nog niet helemaal...

Bij een INNER JOIN worden alleen die rows geselecteerd waarbij het join-veld gelijk is in beide tabellen. Bij een LEFT JOIN worden alle rows aan de LEFT-kant geselecteerd en aan de RIGHT-kant alle records die matchen. Bij geen match wordt het gevuld met NULL. Dit is meer werk dan INNER JOIN, kost dus meer tijd en als ik het goed heb kan de database dat ook minder gebruik maken van de index.

Kijk, bij kleine aantallen maakt het allemaal niet zoveel uit, maar je forum wordt natuurlijk heeel populair en druk, of niet? Als je een INNER JOIN kan gebruiken ipv LEFT, dan is het aan te raden dat te doen. Bij anonimiseren kun je overschakelen van LEFT JOINnaar INNER JOIN, dus ik zou anonimiseren, als er verder geen andere bezwaren zijn.

Edit: en om mijn betoog kracht bij te zetten hier een quote van http://msdn.microsoft.com/en-us/magazine/cc301622.aspx:

Avoid LEFT JOINs and NULLs
There are, of course, times when you need to perform a LEFT JOIN and use NULL values. But they are not a solution for all occasions. Changing the way you structure your SQL queries can mean the difference between a report that takes minutes to run and one that takes only seconds. Sometimes you have to morph the data in a query to look the way your application wants it to look. While the TABLE datatype reduces resource gluttony, there are still plenty of areas in a query that can be optimized. One valuable, commonly used feature of SQL is the LEFT JOIN. It can be used to retrieve all of the rows from a first table and all matching rows from a second table, plus all rows from the second table that do not match the first one. For example, if you wanted to return every Customer and their orders, a LEFT JOIN would show the Customers who did and did not have orders.
This tool can be overused. LEFT JOINs are costly since they involve matching data against NULL (nonexistent) data. In some cases this is unavoidable, but the cost can be high. A LEFT JOIN is more costly than an INNER JOIN, so if you could rewrite a query so it doesn't use a LEFT JOIN, it could pay huge dividends (see the diagram in Figure 1).

Figure 1 Query
Figure 1 Query

One technique to speed up a query that uses a LEFT JOIN involves creating a TABLE datatype and inserting all of the rows from the first table (the one on the left-hand side of the LEFT JOIN), then updating the TABLE datatype with the values from the second table. This technique is a two-step process, but could save a lot of time compared to a standard LEFT JOIN. A good rule is to try out different techniques and time each of them until you get the best performing query for your application.

When you are testing your query's speed, it's important to run it several times and take an average. Your query (or stored procedure) could be stored in the procedure cache in SQL Server's memory and thus would appear to take longer the first time and shorter on all subsequent tries. In addition, other queries could be running against the same tables while your query runs. This could cause your query to stand in line while other queries lock and unlock tables. For example, if you are querying while someone is updating data in that table, your query may take longer to execute while the update commits.

One of the easiest ways to avoid slowdowns with LEFT JOINs is to design the database around them as much as possible. For example, let's assume that a product may or may not have a category. If the product table stores the ID of its category and there was no category for a particular product, you could store a NULL value in the field. Then you would have to perform a LEFT JOIN to get all of the products and their categories. You could create a category with the value of "No Category" and thus specify the foreign key relationship to disallow NULL values. By doing this, you can now use an INNER JOIN to retrieve all products and their categories. While this may seem like a workaround with extra data, this can be a valuable technique as it can eliminate costly LEFT JOINs in SQL batches. Using this concept across the board in a database can save you lots of processing time. Remember, even a few seconds means a lot to your users, and those seconds really add up when you have many users accessing an online database application.

Vervang in het voorbeeld "No category" door "Anonieme user", en je krijgt hetzelfde verhaal.

[Voor 65% gewijzigd door dirkpostma op 12-10-2010 11:37]

[GlowMouse]

radem205 schreef op maandag 11 oktober 2010 @ 20:49:
[...]


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.

Je kunt voor deze zoekopdracht per user een lijst bijhouden van topics waar ze in gepost hebben. Daarnaast kun je het aantal replies in de topictabel opslaan, waarmee de subquery verdwijnt.

RobIII schreef op maandag 11 oktober 2010 @ 21:03:
[...]

Sure, het is meer dan de gemiddelde "select * from mytable" die je in een willekeurige PHP tutorial tegen komt maar voor een beetje RDBMS is dit peanuts.

Not quite. Om deze query uit te voeren moet je bij een user alle topics opvragen waar hij in gepost heeft. Vanwege de date_format voert MySQL de sortering van de resultaten op topicid (nodig voor de GROUP BY) bovendien niet in zijn geheugen maar via een temporary table op de hdd uit (dit omdat de memory storage engine geen variable-length columns aankan, en MySQL a priori geen bovengrens aan kan geven voor het aantal karakters dat uit een functie komt). Voor een paar queries per seconde is dat geen ramp, maar bij veel hosters zal de harddisk van de databaseserver al genoeg gemarteld worden en betekent een temporary disk table dat een query erg langzaam zal zijn.

Gomez12 schreef op maandag 11 oktober 2010 @ 22:52:
[...]

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

Inderdaad, bij een IN zal MySQL de lijst doorlopen totdat de betreffende waarde gevonden wordt elke keer als het criterium gecheckt moet worden; de tijd neemt dus lineair toe met het aantal elementen. Bij een JOIN neemt de tijd, als er gebruik gemaakt worden van een gecombineerde index op (rechtengroep,forum_categorie), logaritmisch toe.

dirkpostma schreef op dinsdag 12 oktober 2010 @ 11:31:
[...]


Bij een INNER JOIN worden alleen die rows geselecteerd waarbij het join-veld gelijk is in beide tabellen. Bij een LEFT JOIN worden alle rows aan de LEFT-kant geselecteerd en aan de RIGHT-kant alle records die matchen. Bij geen match wordt het gevuld met NULL. Dit is meer werk dan INNER JOIN, kost dus meer tijd en als ik het goed heb kan de database dat ook minder gebruik maken van de index.

De enige reden waarom INNER JOIN sneller zou zijn, is omdat MySQL de joinvolgorde om kan draaien als hij denkt dat dat sneller is. De tijd die het kost om een veld met NULL te vullen is verwaarloosbaar klein.