[MySQL] Optimalisatie voor SELECT query

[gvanh]

Inmiddels ben ik al een aantal maanden bezig met de optimalisatie van een MySQL query. Specifiek voor dit doel heb ik inmiddels MySQL 4.1 geinstalleerd, zodat ik ook subqueries tot m'n beschikking heb ... het lukt me alleen niet om op een slimme manier de juiste info uit de table te halen ... de huidige methode geeft query-tijden van meer dan een minuut als de data in de tabellen ook maar enigszins flink wordt.

Ten eerste de tabel-layout (in simpele vorm)

SQL:

CREATE TABLE `cms_objects` (
  `cms_objectID` int(11) NOT NULL auto_increment,
  `caption` varchar(255) NOT NULL default '',
  `parentID` int(11) NOT NULL default '0',
  `lft` int(11) NOT NULL default '0',
  `rgt` int(11) NOT NULL default '0',
  `depth` int(11) NOT NULL default '0'
)

Het idee is dat alle objecten in m'n CMS zijn opgeslagen in deze objecten tabel, waarbij object-specifieke gegevens worden opgeslagen in tabellen die specifiek voor het type object zijn ingedeeld (pages, messages, images, etc). Door gebruik te maken van het left/right systeem kan ik heel makkelijk in 1 keer een hele "familie" van objecten uit de database halen.

Nu is het met het rechten-systeem zo geregeld, dat voor ieder object en per gebruiker/gebruikersgroep in een aparte tabel kan worden ingesteld of er bepaalde rechten (LEZEN/SCHRIJVEN/etc.) toegezegd of juist ontzegd worden, die tabel ziet er zo uit:

SQL:

CREATE TABLE `cms_objects2operations` (
  `cms_objectID` int(11) NOT NULL default '0',
  `auth_roleID` int(11) NOT NULL default '0',
  `auth_operationID` int(11) NOT NULL default '0',
  `value` int(2) NOT NULL default '0',
  PRIMARY KEY  (`cms_objectID`,`auth_roleID`,`auth_operationID`)
)

De rechten werken vervolgens hierarchisch, wat wil zeggen dat als er voor een bepaald object geen specifieke rechten zijn ingesteld, dat gelden automatisch de rechten van de PARENT van het betreffende object (en zo verder omhoog tot het TOPLEVEL object).

De kern van het probleem is dus om in 1 query alle objecten, inclusief hun PARENTS uit de tabel te halen. Vervolgens kan ik die informatie koppelen aan de rechten die al dan niet zijn ingesteld. De query die ik nu gebruik is als volgt:

SQL:

SELECT

    o1.cms_objectID, o2.cms_objectID, o2.depth, o1.*

FROM

    cms_objects o1, cms_objects o2
    
WHERE

    o1.lft > o2.lft
    
    AND o1.rgt < o2.rgt
    
    AND o1.depth > o2.depth

Zoals je wellicht al aanvoelt duurt het veel te lang om die uit te voeren.

Ik heb geen flauw idee meer hoe ik het dan moet aanpakken.

Alvast heel veel dank voor alle tips en hulp.

[whoami]

Je hebt een cartesiaans product, aangezien je data uit 2 tabellen selecteert, en nergens gaat gaan specifieren welke gegevens uit tabel A te maken hebben met gegevens uit tabel B.

Met SQL is het afaik onmogelijk om met 1 query een hele 'tree' uit de DB te halen. AFAIK is het niet mogelijk om hetgeen jij wilt doen, in 1 query te verwezenlijken.

[ Voor 33% gewijzigd door whoami op 26-09-2005 16:58 ]

[jochemd]

Hoe wordt je lft / rgt systeem geacht te werken? Als een standaard nested set?

[gvanh]

@jochemd

Ja, moet inderdaad als nested set werken ... zojuist heb ik nog een beschrijving daarvan gevonden op http://dev.mysql.com/tech...es/hierarchical-data.html ... die had ik nog niet eerder gezien, daarvóór heb ik mijn kennis voornamelijk uit het alombekende SitePoint artikel gehaald.

Na lezen van het artikel op dev.MySQL.com heb ik al wel de query kunnen "optimaliseren" tot het volgende:

SQL:

SELECT o1.caption

FROM cms_objects AS o1,

cms_objects AS o2

WHERE o1.lft BETWEEN o2.lft AND o2.rgt

ORDER BY o1.lft;

In mijn "grote" dataset om te testen (niet extreem groot ... momenteel 7000 rows) ... heb ik met die query al de benodigde tijd weten terug te brengen van ruim 70 seconden tot 10 seconde. Dat scheelt dus al ... maar wellicht kan dit nog handiger?

[ACM]

En wat voor indexen heb je op die tabel?
Een op lft? Een op lft, rgt gecombineerd?

Maar sowieso snap ik niet zo goed dat je de boel joined op deze manier, want je moet alle elementen toch ophalen. Waarom dan alle subtrees extra dubbel opvragen?

Met alleen SELECT o1.caption, o1.lft, o1.rgt ... ORDER BY o1.lft;
kan je toch de tree die je alsnog in je geheugen moet bouwen ook gewoon opbouwen?

[jochemd]

Qua SQL syntax is dit volgens mij optimaal met een standaad netsed set. Deze versie gaat namelijk een stuk beter om met de beschikbare indexen dan je eerste versie. Heb je al een index over alleen lft en een index over zowel lft en rgt? Vergeet ook niet om die indexen uniek te maken.

[gvanh]

Maar sowieso snap ik niet zo goed dat je de boel joined op deze manier, want je moet alle elementen toch ophalen. Waarom dan alle subtrees extra dubbel opvragen?

Ik wil straks per subtree weten wat het meest "diepe" object is waarvoor een permissie is ingesteld in de "objects2permissions" table. Wanneer namelijk voor object X geen permissies zijn ingesteld, dan moet ik dus weten wat de permissies zijn op zijn PARENT (en zo verder omhoog). Dat betekent dus dat ik voor alle subtrees moet uitvinden wat de meest toepasselijke permissies zijn. Vandaar dat ik alle subtrees uit de DB haal. Vervolgens ga ik ze groupen ... alwaar het tweede probleem zich voordoet, namelijk de ROW met de maximale DEPTH uit de database halen ... en daarvoor heb ik dus subqueries nodig ... want dat lukt anders niet in één enkele query.

Heb je al een index over alleen lft en een index over zowel lft en rgt? Vergeet ook niet om die indexen uniek te maken.

Ik heb bij deze de aangegeven INDEXen aangemaakt (ik had er één, voor lft/rgt gecombineerd). Hoe maak ik een index UNIQUE? Standaard zie ik die optie in PhpMyAdmin niet terugkomen ... ik zal voor de syntax wel even de handleiding doorkijken.

[ Voor 20% gewijzigd door gvanh op 26-09-2005 18:44 ]