[MySQL] Traaag?!

[gvanh]

Hallo!

Momenteel ben ik bezig met een SELECT statement over 4 tabellen in een MySQL database die over de betreffende tabellen ongeveer 50.000 records telt. Ongeveer iedere JOIN die ik echter uitvoer, kost de database minimaal zo'n 60 seconde om iets van een antwoord terug te geven. Is dat normaal bij een database van deze grootte? Ik kan me dat toch niet voorstellen.

De tabellen in de database zijn ongeveer als volgt:

code:

TABEL invoer_resultaten (
  klantnr (INT 11)
  tijd (timestamp)
  resultaat (VARCHAR)
)

TABEL klanten (
  klantnr  (VARCHAR 255)
  POSTCODE (VARCHAR 255)
  HUISNUM (VARCHAR 255)
  HUISNUMTOE (VARCHAR 255)
)

TABEL conversion (
  newID (INT 11)
  oldID (INT 11)
)

De tabel conversion wordt momenteel gebruikt als opsomming van alle klantnrs die geldig zijn voor de eind-rapportage.

Nu wil ik graag alle klanten + resulaten hebben die vallen binnen de periode 23-April-2005 t/m 3-Mei-2005, waarvan de resultaat code-combinatie ofwel (9 + 20), ofwel (24 + 20) ofwel (19) is EN waarvan het klantnr voorkomt als newID in de tabel conversion.

Mijn Query daarvoor is als volgt geworden:

code:

SELECT
  DISTINCT(RS1.klantnr), K.POSTCODE, K.HUISNR, K.HUISNRTOE,
  date_format(RS1.tijd, '%e-%c-%y 00:00:00') AS datum,
  RS1.resultaat AS rs1, RS2.resultaat AS rs2
FROM
  invoer_resultaten AS RS1, invoer_resultaten AS RS2, klanten as K,
  conversion AS C
WHERE
  RS1.klantnr = K.klantnr
  AND RS2.klantnr = K.klantnr
  AND UNIX_TIMESTAMP(RS1.tijd) >= 1114207200
  AND UNIX_TIMESTAMP(RS1.tijd) < 1115157600
  AND UNIX_TIMESTAMP(RS2.tijd) >= 1114207200
  AND UNIX_TIMESTAMP(RS2.tijd) < 1115157600
  AND (
    (RS1.resultaat = 9 AND RS2.resultaat = 20)
    OR (RS1.resultaat = 20 AND RS2.resultaat = 24)
    OR (RS1.resultaat = 19 AND RS2.resultaat = 19)
  )
  AND RS1.contact_id >= RS2.contact_id
  AND K.klantnr = C.newID
ORDER BY RS1.contact_id DESC, RS1.klantnr

Ik haal dus de resultaten records 2 keer uit de database, zodat ik de verschillende combinaties kan controleren.

Deze query uitvoeren duurt inmiddels ongeveer 5 minuten. Kan dat anders/sneller/beter.

Overigens is het database-ontwerp een gegeven (het klantnr als VARCHAR 255 bijvoorbeeld). Die heb ik niet zelf zo opgezet.

Iedere suggestie is van harte welkom!

[ Voor 4% gewijzigd door gvanh op 16-05-2005 15:31 ]

[Verwijderd]

Dit klinkt als een database waarin helemaal geen indices voorkomen. Maak eens een index aan voor alle foreign key kolommen?

[edit]
Een klantnummer als VARCHAR en primary key. Juist ja, niet echt handig dus.

[ Voor 27% gewijzigd door Verwijderd op 16-05-2005 15:32 ]

[Skaah]

Begin maar met

SQL:

ALTER TABLE klanten MODIFY COLUMN klantnr INT(11) NOT NULL;

[gvanh]

Wat zijn de foreign key kolommen? Alle kolommen die een verwijzing bevatten naar records in andere tabellen?

Wegens zelf-prutserij ben ik nooit zo goed in teminologie.

[gvanh]

@SKAAH:
Ben ik inderdaad nu aan het doen ... maar ook dat duurt overigens BELACHELIJK lang ... MySQL is nu toch al weer zeker 5 minuten aan het nadenken ... dat kan toch niet goed zijn? Overigens draait de boel hier nu in testomgeving op mijn eigen Linux-bak ... dus van over-belasting van de server kan geen sprake zijn...

[whoami]

gvanh schreef op maandag 16 mei 2005 @ 15:39:
@SKAAH:
Ben ik inderdaad nu aan het doen ... maar ook dat duurt overigens BELACHELIJK lang ... MySQL is nu toch al weer zeker 5 minuten aan het nadenken ... dat kan toch niet goed zijn? Overigens draait de boel hier nu in testomgeving op mijn eigen Linux-bak ... dus van over-belasting van de server kan geen sprake zijn...

Niet vergeten dat al die 50.000 records moeten ge-updated worden....

Zorg erdus voor dat je indexen hebt op je PK columns, op je FK columns, en op columns waar je veel op zoekt.

[ Voor 11% gewijzigd door whoami op 16-05-2005 15:46 ]

[T-MOB]

gvanh schreef op maandag 16 mei 2005 @ 15:37:
Wat zijn de foreign key kolommen? Alle kolommen die een verwijzing bevatten naar records in andere tabellen?

Wegens zelf-prutserij ben ik nooit zo goed in teminologie.

Als primary key leg je een index in elke tabel op een kolom die het record uniek identificeert. In de tabel klanten dus op de kolom klantnr.Foreign keys leg je inderdaad in de andere tabellen op de kolom die naar dit klantnr verwijst. Dit geeft een relatie aan.

Voor MySQL moet je er daarnaast nog rekening mee houden dat bij een join slechts 1 index kan worden gebruikt. Het toevoegen van indices over meerdere kolommen die je gezamelijk nodig hebt kan flink performance schelen...

Tot slot nog over de manier van joinen. Je joint nu impliciet in je WHERE clausule. Ik heb me hier op het forum laten vertellen dat dit een ouderwetse "niet zo volgens de standaard" methode is. Voor de performance is dat niet heel boeiend an sich - het effect is hetzelfde. Maar in sommige gevallen kun je nog wat winnen door een LEFT join te gebruiken ipv een INNER JOIN

[gvanh]

Heb ik het dan goed begrepen dat het geen zin heeft om een INDEX aan te maken op een veld dat al gebruikt wordt als PRIMARY KEY?

[Wolfboy]

gvanh schreef op maandag 16 mei 2005 @ 15:50:
Heb ik het dan goed begrepen dat het geen zin heeft om een INDEX aan te maken op een veld dat al gebruikt wordt als PRIMARY KEY?

Klopt, een primary key bevat al een index.

Primary key staat gelijk aan unique + index + NOT NULL

[ Voor 2% gewijzigd door Wolfboy op 16-05-2005 15:56 . Reden: not null toegevoegd ]

[jochemd]

gvanh schreef op maandag 16 mei 2005 @ 15:30:
Momenteel ben ik bezig met een SELECT statement over 4 tabellen in een MySQL database die over de betreffende tabellen ongeveer 50.000 records telt. Ongeveer iedere JOIN die ik echter uitvoer, kost de database minimaal zo'n 60 seconde om iets van een antwoord terug te geven. Is dat normaal bij een database van deze grootte?

Op een 486?

De tabellen in de database zijn ongeveer als volgt:

En hoe zijn ze exact? Inclusief indexen graag.

Nu wil ik graag alle klanten + resulaten hebben die vallen binnen de periode 23-April-2005 t/m 3-Mei-2005

Welk deel van de records voldoet daaraan?

waarvan de resultaat code-combinatie ofwel (9 + 20), ofwel (24 + 20) ofwel (19) is

Welk deel van de records voldoet daaraan?

EN waarvan het klantnr voorkomt als newID in de tabel conversion.

Welk deel van de records voldoet daaraan?

code:

SELECT
  DISTINCT(RS1.klantnr), K.POSTCODE, K.HUISNR, K.HUISNRTOE,
  date_format(RS1.tijd, '%e-%c-%y 00:00:00') AS datum,
  RS1.resultaat AS rs1, RS2.resultaat AS rs2
FROM
  invoer_resultaten AS RS1, invoer_resultaten AS RS2, klanten as K,
  conversion AS C
WHERE
  RS1.klantnr = K.klantnr
  AND RS2.klantnr = K.klantnr
  AND UNIX_TIMESTAMP(RS1.tijd) >= 1114207200
  AND UNIX_TIMESTAMP(RS1.tijd) < 1115157600
  AND UNIX_TIMESTAMP(RS2.tijd) >= 1114207200
  AND UNIX_TIMESTAMP(RS2.tijd) < 1115157600
  AND (
    (RS1.resultaat = 9 AND RS2.resultaat = 20)
    OR (RS1.resultaat = 20 AND RS2.resultaat = 24)
    OR (RS1.resultaat = 19 AND RS2.resultaat = 19)
  )
  AND RS1.contact_id >= RS2.contact_id
  AND K.klantnr = C.newID
ORDER BY RS1.contact_id DESC, RS1.klantnr

Waarom heb je hier het hele UNIX_TIMESTAMPS circus in? Op deze manier kan daar toch geen index voor gebruikt worden (in MySQL)?

Deze query uitvoeren duurt inmiddels ongeveer 5 minuten. Kan dat anders/sneller/beter.

Vast wel. Kan je ook de EXPLAIN output nog even geven?

[jochemd]

Wolfboy schreef op maandag 16 mei 2005 @ 15:51:
Primary key staat gelijk aan unique + index.

+ NOT NULL

[Wolfboy]

jochemd schreef op maandag 16 mei 2005 @ 15:54:
[...]

+ NOT NULL

Oeps, die was ik vergeten idd

[Johnny]

gvanh schreef op maandag 16 mei 2005 @ 15:50:
Heb ik het dan goed begrepen dat het geen zin heeft om een INDEX aan te maken op een veld dat al gebruikt wordt als PRIMARY KEY?

Een primary key is een index waarbij waarvan iedere rij uniek is en waarvan je er maar 1 per tabel kan hebben.

Dus nog een index toevoegen op diezelfde kolom is een beetje dubbelop.

Trouwens, waarom gebruik je overal varchar(255) als datatype? Een postcode of huisnummer zal nooit 255 tekens lang zijn!

[gvanh]

Nog even een update met wat ik nu heb gedaan (DANK VOOR DE VELE TIPS EN HULP!)

Zojuist een INDEX aangemaakt in de tabel "invoer_resultaten" op de velden (klantnr
tijd en resultaat) -> een gezamenlijke INDEX dus. Dit naar aanleiding van de tip dat in MySQL per query maar 1 INDEX gebruikt kan worden en omdat in de query op deze drie velden wordt vergeleken.

De UNIX_TIMESTAMP boel heb ik, omdat ik niet goed weet hoe je exact op tijd kunt vergelijken in MySQL zelf ... kan ik ook gewoon in m'n query opnemen:

RS1.tijd >= 20050418204610?

Dan ga ik dat ook even invoegen.

Nogmaals: heel veel dank ... te gek!

@JOHNNY: I know! Ik heb dan ook inderdaad zelf niet het ontwerp van de DB opgezet ... ik ben slechts ingeroepen om hierop rapportages los te laten. Ook met betrekking tot overige - hier niet relevante - tabellen is er nog wel wat te behalen met de standaard regels voor normalisatie. Maar goed ... dat is wellicht een volgende stap.

[ Voor 23% gewijzigd door gvanh op 16-05-2005 15:59 ]

[whoami]

T-MOB schreef op maandag 16 mei 2005 @ 15:49:
[...]
Maar in sommige gevallen kun je nog wat winnen door een LEFT join te gebruiken ipv een INNER JOIN

Juist niet; een OUTER join is trager dan een INNER join. Gebruik dus enkel een outer (left of right) join als het echt nodig is.

[bartvb]

Wat jochemd zegt

EXPLAIN output is _erg_ handig. Zorg er verder ook voor dat je niet met de standaard settings van MySQL draait. Standaard staat MySQL zo ingesteld dat het zelfs op een 286 met 2MB geheugen werkt, als je dan 1GB aan geheugen hebt wordt daar totaal geen gebruik van gemaakt.

Die enorme lijst met UNIX_TIMESTAMP dingen is ook totaal niet handig, reken je timestamp in de applicatie om naar het formaat in MySQL, niet andersom. Nu moet MySQL iets van 100.000 datums omrekenen.

50k rows is niet bepaald veel voor MySQL zolang je zorgt dat de key buffer groot genoeg is en je fatsoenlijke indices hebt.

edit:
Ik ben te langzaam

je kan gewoon iets doen als:

tijd BETWEEN '2004-1-12 12:12' AND '2004-12-12 12:12'
of
tijd > '2004-12-12 12:12'

[ Voor 12% gewijzigd door bartvb op 16-05-2005 16:03 ]

[jochemd]

gvanh schreef op maandag 16 mei 2005 @ 15:57:
De UNIX_TIMESTAMP boel heb ik, omdat ik niet goed weet hoe je exact op tijd kunt vergelijken in MySQL zelf ... kan ik ook gewoon in m'n query opnemen:

RS1.tijd >= 20050418204610?

Bijna.

[T-MOB]

gvanh schreef op maandag 16 mei 2005 @ 15:57:
Nog even een update met wat ik nu heb gedaan (DANK VOOR DE VELE TIPS EN HULP!)

Zojuist een INDEX aangemaakt in de tabel "invoer_resultaten" op de velden (klantnr
tijd en resultaat) -> een gezamenlijke INDEX dus. Dit naar aanleiding van de tip dat in MySQL per query maar 1 INDEX gebruikt kan worden en omdat in de query op deze drie velden wordt vergeleken.

Zo erg is het ook weer niet... het gaat om 1 index per gejoinde tabel

Om te zien of je query nog bottlenecks heeft qua indices kun je hem nog een keer uitvoeren met EXPLAIN ervoor. Je krijgt dan te zien welke indices worden gebruikt per tabel. Erg nuttig bij het optimaliseren van (met name) de wat lompere queries...

[T-MOB]

whoami schreef op maandag 16 mei 2005 @ 16:01:
[...]
Juist niet; een OUTER join is trager dan een INNER join. Gebruik dus enkel een outer (left of right) join als het echt nodig is.

Ik neem direct van je aan dat het zo is (in elk geval theoretisch). De opmerking was gebaseerd op een benchmarkje tijdens het optimaliseren van een eigen query. Die was na het vervangen van één INNER JOIN door een LEFT JOIN significant sneller. Zal kijken of ik de code nog terug kan vinden...

[gvanh]

Werkt wel gewoon, die tijd vergelijking die ik daaronder heb neergezet. De gelinkte pagina heb ik inmiddels ook al flink wat keren doorgeneusd ... wat voorbeelden betreft wat magertjes ... alleen tussen de commentaren vind ik direct wat ik nodig heb...

Hoe dan ook!

Inmiddels net weer even de query opnieuw gedraaid ... en hij is nu AANZIENLIJK sneller ... nu nog slechts ongeveer een seconde of 3 nodig voor het hele verhaal.

Ik ga 'ns kijken wat dat EXPLAIN doet ...

[_Sunnyboy_]

Doe dat. Ik heb zelf met indexen een query wel eens van 120seconden naar 0.05 seconden gekregen door een uurtje proberen.

[Wolfboy]

whoami schreef op maandag 16 mei 2005 @ 16:01:
Juist niet; een OUTER join is trager dan een INNER join. Gebruik dus enkel een outer (left of right) join als het echt nodig is.

Niet in het geval van mysql helaas, daar is de left join zeker het snelst (tenminste, dat zijn mijn bevindingen)

[jochemd]

Wolfboy schreef op maandag 16 mei 2005 @ 16:14:
[...]
Niet in het geval van mysql helaas, daar is de left join zeker het snelst (tenminste, dat zijn mijn bevindingen)

Ik neem aan dat je die bij http://bugs.mysql.com/ hebt aangemeld?

[Wolfboy]

jochemd schreef op maandag 16 mei 2005 @ 16:25:
Ik neem aan dat je die bij http://bugs.mysql.com/ hebt aangemeld?

Nee dat heb ik niet gedaan eigenlijk, ik vind het een beetje lastig om er een goede testcase van te maken namelijk.

[gvanh]

Dan nu een ander puntje waar ik tegenaan loop. Ik krijg nu met een bepaalde query een record-set, waarin - ondanks de DISTINCT - 4 rows in voorkomen met dezelfde klantid. Nu wil ik graag slechts de rows overhouden waarvoor geldt dat de datum het laatst is.

Kan dat?

[curry684]

Wolfboy schreef op maandag 16 mei 2005 @ 16:14:
[...]
Niet in het geval van mysql helaas, daar is de left join zeker het snelst (tenminste, dat zijn mijn bevindingen)

Lijkt me onzin, een INNER JOIN is een pure indexmatch in tandem, terwijl een OUTER JOIN ook nog 'misses' erbij moet zoeken. Zou een grove performancebug zijn als de OUTER sneller is.

gvanh schreef op maandag 16 mei 2005 @ 17:19:
Dan nu een ander puntje waar ik tegenaan loop. Ik krijg nu met een bepaalde query een record-set, waarin - ondanks de DISTINCT - 4 rows in voorkomen met dezelfde klantid. Nu wil ik graag slechts de rows overhouden waarvoor geldt dat de datum het laatst is.

Kan dat?

DISTINCT filtert per spec alleen compleet unieke rows. Als je een specifieke row wil selecteren afhankelijk van een conditie binnen de betreffende subset heb je een correlated subquery nodig (en dus MySQL 4.1 of later). Dan kijk je naar dit soort constructies:

SQL:

SELECT * FROM Tabel t1
WHERE Date = (SELECT max(Date) FROM Tabel t2 WHERE t1.id = t2.id);

Mocht MySQL braken op correlated subqueries (zou me niet verbazen) kan het ook nog iets minder performant:

SQL:

SELECT * FROM Tabel
WHERE id IN (SELECT max(Date) FROM Tabel GROUP BY klantid);

[Creepy]

gvanh schreef op maandag 16 mei 2005 @ 17:19:
Dan nu een ander puntje waar ik tegenaan loop. Ik krijg nu met een bepaalde query een record-set, waarin - ondanks de DISTINCT - 4 rows in voorkomen met dezelfde klantid. Nu wil ik graag slechts de rows overhouden waarvoor geldt dat de datum het laatst is.

Kan dat?

Ja dat kan. Daarvoor heb je MAX() en GROUP BY. En dat wordt netjes uitgelegd in een beetje SQL tutorial

[Wolfboy]

curry684 schreef op maandag 16 mei 2005 @ 17:30:
Lijkt me onzin, een INNER JOIN is een pure indexmatch in tandem, terwijl een OUTER JOIN ook nog 'misses' erbij moet zoeken. Zou een grove performancebug zijn als de OUTER sneller is.

Daarom vind ik het ook lastig om een testcase ervan te maken, ik ben het een paar keer tegengekomen maar het is niet altijd zo nee.

Het kan ook zijn dat er iets verkeerd zat met de query toen maar dat weet ik niet zeker, het is inmiddels alweer een tijdje geleden en tegenwoordig gebruik ik (gelukkig) bijna alleen nog maar postgresql

[gvanh]

code:

SELECT
  DISTINCT(RS1.klantnr), K.POSTCODE, K.HUISNR, K.HUISNRTOE, MAX(RS1.tijd),
  date_format(RS1.tijd, '%e-%c-%y 00:00:00') AS datum,
  RS1.resultaat AS rs1, RS2.resultaat AS rs2
FROM
  invoer_resultaten AS RS1, invoer_resultaten AS RS2, klanten as K,
  conversion AS C
WHERE
  RS1.klantnr = K.klantnr AND RS2.klantnr = K.klantnr AND RS1.tijd >= 20050101120000
  AND RS1.tijd < 20050531120000
  AND RS2.tijd >= 20050101120000
  AND RS2.tijd < 20050531120000
  AND (
    (RS1.resultaat = 9 AND RS2.resultaat = 20)
    OR (RS1.resultaat = 24 AND RS2.resultaat = 20)
    OR (RS1.resultaat = 19 AND RS2.resultaat = 19)
  )
  AND K.klantnr = C.newID
  AND (RS1.klantnr = 1012632 OR RS1.klantnr = 4076823)
  AND RS1.klantnr != -1 AND RS2.klantnr != -1
  GROUP BY RS1.tijd
  ORDER BY RS1.klantnr, RS1.tijd

Dit is mijn query voor het moment, toegespitst op een tweetal specifieke klantnummers waarbij het steeds fout lijkt te gaan (1012632 en 4076823). En - ondanks het gebruik van de MAX() en GROUP BY onderdelen, krijg ik nog steeds sets voor dubbele klantnummers. Ook GROUP BY RS1.klantnr, RS2.klantnr, K.klantnr biedt daarbij geen soelaas.

Iemand die toevallig ziet wat ik fout doe?

Alvast weer veeeel dank!

[curry684]

Uh ja dude ga lekker zelf debuggen. De mensen hier hebben ook betere dingen te doen dan jou 5 uur aan het handje te nemen en stap voor stap jouw query te bouwen. Lees [rml]curry684 in "[ MySQL] Traaag?!"[/rml] anders eerst even, daar heb je nog niets aan veranderd.

[gvanh]

Ja, je hebt gelijk. Helaas werken alleen de door jouw gesuggereerde SUB-QUERIES niet, aangezien ik hier op 4.0.12 aan het werk ben, en de LIVE database ook niet verder komt dan deze versie. Dat is dus - too bad - geen optie.

Toch veel dank!

[curry684]

Dan zou ik toch maar eens kijken of je de DB niet kunt upgraden, want zonder subqueries ga je dit specifieke geval niet in 1 query kunnen.

[gvanh]

Nee, dat begin ik ook te beseffen. Ik heb nu in PHP een "loopje" gemaakt, dat dubbele entries controleert en alleen de (chronologisch gezien) laatste behoudt.

Daarmee was ik klaar in een seconde of 30 ... ... wellicht had ik dat toch eerder moeten doen.

[T-MOB]

curry684 schreef op maandag 16 mei 2005 @ 17:30:
[...]
Lijkt me onzin, een INNER JOIN is een pure indexmatch in tandem, terwijl een OUTER JOIN ook nog 'misses' erbij moet zoeken. Zou een grove performancebug zijn als de OUTER sneller is.

Die performance-bug zit er helaas wel in, dan. Ik heb de query waar ik een inner door een left veranderd heb weer even opgezocht om te benchmarken. De joins in de volgende query zijn zowel left als inner gejoined voor de test:

PHP:

$selID = array ('0001', '0034', '0002', '0005', '0014', '0031', '0021');

$sql['inner-inner'] = '
 SELECT DISTINCT pge_vars.name, pge_vars.label, pge_vars.variableid, pge_vars.groupid, pge_vargroups.name as groupname 
 FROM pge_vars 
 INNER JOIN pge_vargroups
 ON (pge_vars.groupid = pge_vargroups.groupid)
 INNER JOIN pge_datasetvars
 ON (pge_vars.variableid = pge_datasetvars.variableid)
 WHERE pge_datasetvars.datasetid IN ("' .implode('","', $selID) .'")
 ORDER BY pge_vargroups.order ASC, pge_vars.variableid ASC';

De constructie met de array is gebruikt omdat de uitkomst nogal verschilt met het aantal id's en de hoeveelheid rijen die ervoor moeten worden gejoined.

Uitkomsten (MySQL 3.23.53-max-nt):

## 1 id gebruikt
inner-inner: 0.905512094498s (69 rows)
inner-left:  3.48609399796s  (69 rows)
left-inner:  0.978485822678s (69 rows)
left-left:   4.00124597549s  (69 rows)

## 4 id's gebruikt
inner-inner: 3.26122403145s (142 rows)
inner-left:  2.2806789875s  (142 rows)
left-inner:  3.54559803009s (142 rows)
left-left:   2.79622197151s (142 rows)

## 7 id's gebruikt
inner-inner: 5.67796087265s (151 rows)
inner-left:  2.2815349102s  (151 rows)
left-inner:  6.37572002411s (151 rows)
left-left:   2.8088350296s  (151 rows)

## 10 id's gebruikt
inner-inner: 6.67048501968s (174 rows)
inner-left:  2.3371720314s  (174 rows)
left-inner:  7.67729592323s (174 rows)
left-left:   2.88050413132s (174 rows)

Mijn excuses voor het maken van een niet te reproduceren test. Het geval is gepakt uit een real-world applicatie waar ik het probleem tegen kwam. Het opzetten van test-databases met dummydata was me iets te veel werk om te laten zien dat een LEFT sneller kan zijn (danwel dat MySQL af en toe een wappie database is )

[ Voor 5% gewijzigd door T-MOB op 16-05-2005 19:23 ]

[jochemd]

T-MOB schreef op maandag 16 mei 2005 @ 19:21:

PHP:

$selID = array ('0001', '0034', '0002', '0005', '0014', '0031', '0021');

$sql['inner-inner'] = '
 SELECT DISTINCT pge_vars.name, pge_vars.label, pge_vars.variableid, pge_vars.groupid, pge_vargroups.name as groupname 
 FROM pge_vars 
 INNER JOIN pge_vargroups
 ON (pge_vars.groupid = pge_vargroups.groupid)
 INNER JOIN pge_datasetvars
 ON (pge_vars.variableid = pge_datasetvars.variableid)
 WHERE pge_datasetvars.datasetid IN ("' .implode('","', $selID) .'")
 ORDER BY pge_vargroups.order ASC, pge_vars.variableid ASC';

Uitkomsten (MySQL 3.23.53-max-nt):

## 1 id gebruikt
inner-inner: 0.905512094498s (69 rows)
inner-left:  3.48609399796s  (69 rows)
left-inner:  0.978485822678s (69 rows)
left-left:   4.00124597549s  (69 rows)

## 4 id's gebruikt
inner-inner: 3.26122403145s (142 rows)
inner-left:  2.2806789875s  (142 rows)
left-inner:  3.54559803009s (142 rows)
left-left:   2.79622197151s (142 rows)

## 7 id's gebruikt
inner-inner: 5.67796087265s (151 rows)
inner-left:  2.2815349102s  (151 rows)
left-inner:  6.37572002411s (151 rows)
left-left:   2.8088350296s  (151 rows)

## 10 id's gebruikt
inner-inner: 6.67048501968s (174 rows)
inner-left:  2.3371720314s  (174 rows)
left-inner:  7.67729592323s (174 rows)
left-left:   2.88050413132s (174 rows)

Dat is inderdaad een planner probleempje

Je ziet duidelijk dat de tijd voor inner-inner evenredig is met het aantal IDs in de array. Dat is consistent met een executie plan waarbij de scan van de tabel met deze IDs 'driving' is. Zonder voor de rest iets van de distributie van waardes in deze tabellen af te weten is dat ook het meest logische plan. In de EXPLAIN zal je zien dat alle inner-inner joins pge_datasetvars -> pge_vargroups -> pge_vars als plan hebben.
Als je een left join hebt leg je daarmee vast dat de linker tabel de driving tabel is. In de left-left joins ligt dus vast dat de volgorde pge_vars -> pge_vargroups -> pgedatasetvars is (left-inner en inner-left laat ik als exercitie voor de lezer). Kennelijk is dat een plan dat onverwacht efficient is. Is pge_vargroups misschien heel erg klein?

[T-MOB]

jochemd schreef op maandag 16 mei 2005 @ 23:46:
[...]
Kennelijk is dat een plan dat onverwacht efficient is. Is pge_vargroups misschien heel erg klein?

pge_vargroups is idd erg klein, de boel ziet er ongeveer zo uit:

pge_vargroups (27 records):
| groupid | order | name |
--------------------------
| (PK)    | ...   | (FT) |

pge_vars (317 records): 
| variableid | groupid | zut...
-------------------------------
| (PK)       | (FK)    | ...

pge_datasetvars (2338 records):
| datasetid | variableid | zut...
---------------------------------
| (FK)      | (FK)       | ...

Maar als ik je goed begrijp - met excuses voor het kapen van het topic - dan zou het nog sneller kunnen als ik de tabel-volgorde in de query aanpas en INNER JOINS gebruik. Dat gaan we morgen sowieso even testen...

[jochemd]

T-MOB schreef op dinsdag 17 mei 2005 @ 02:19:

Maar als ik je goed begrijp - met excuses voor het kapen van het topic - dan zou het nog sneller kunnen als ik de tabel-volgorde in de query aanpas en INNER JOINS gebruik.

Je moet gebruik maken van SELECT STRAIGHT_JOIN zodat je MySQL kan dwingen om een bij een inner join het zelfde plan te gebruiken als bij een left join. Daarmee kan je elke inner join minimaal even snel maken als de corresponderende left join.
Ik zou in jouw geval dus het volgende eens proberen:

code:

 SELECT STRAIGHT JOIN DISTINCT pge_vars.name, pge_vars.label, pge_vars.variableid, pge_vars.groupid, pge_vargroups.name as groupname 
 FROM pge_vars 
 INNER JOIN pge_vargroups
 ON (pge_vars.groupid = pge_vargroups.groupid)
 INNER JOIN pge_datasetvars
 ON (pge_vars.variableid = pge_datasetvars.variableid)
 WHERE pge_datasetvars.datasetid IN ("' .implode('","', $selID) .'")
 ORDER BY pge_vargroups.order ASC, pge_vars.variableid ASC';

[curry684]

jochemd schreef op dinsdag 17 mei 2005 @ 08:11:
[...]
Je moet gebruik maken van SELECT STRAIGHT_JOIN zodat je MySQL kan dwingen om een bij een inner join het zelfde plan te gebruiken als bij een left join. Daarmee kan je elke inner join minimaal even snel maken als de corresponderende left join.

Jakkes Iemand een reproducable dataset voor dit probleem? Ben wel benieuwd wat SQL Server ervan bakt eigenlijk

[_js_]

T-MOB schreef op maandag 16 mei 2005 @ 16:05:
[...]


Zo erg is het ook weer niet... het gaat om 1 index per gejoinde tabel

Dat klopt niet helemaal.
Er wordt 1 index gebruikt voor WHERE voorwaarden, GROUP BY en SORT BY. Dus als je filtert op tijd en klantnummer en sorteert op contact_id, dan heb je dus een gecombineerde index nodig op tijd, klantnummer en contact_id.

De één index per join is puur alleen voor de joins. Stel dat MySQL begint met R1 tabel, en daar de R2 tabel aan wil linken. Dan filtert en sorteert hij de R1 tabel, en gaat dan per rij in de resultaten een rij in R2 opzoeken, eventueel met behulp van een index op een kolom in R2. De index in R2 wordt dus alleen voor die join gebruikt, en nergens anders voor.

[T-MOB]

curry684 schreef op dinsdag 17 mei 2005 @ 09:26:
[...]
Jakkes Iemand een reproducable dataset voor dit probleem? Ben wel benieuwd wat SQL Server ervan bakt eigenlijk

U vraagt wij draaien .. Ik heb een aftrekseltje gemaakt van mijn tabellen. Dat is, wat nuttige data vervangen door bogus met dezelfde lay-out. clickez...

[Wolfboy]

T-MOB schreef op dinsdag 17 mei 2005 @ 13:21:
U vraagt wij draaien .. Ik heb een aftrekseltje gemaakt van mijn tabellen. Dat is, wat nuttige data vervangen door bogus met dezelfde lay-out. clickez...

offtopic:
Waarom geef je die bestanden de csv extensie als ze niet comma separated zijn?

[T-MOB]

Wolfboy schreef op dinsdag 17 mei 2005 @ 13:40:
[...]

offtopic:
Waarom geef je die bestanden de csv extensie als ze niet comma separated zijn?

offtopic:
A:Omdat OpenOffice.org ze anders opent met de writer ipv de spreadsheet..

Q:Waarom maak je ze dan niet gewoon comma-seperated?
A:Omdat de | veel makkelijker verwerkt in een applicatie, hoef je niets voor te escapen etc. TAB als seperator zou ook kunnen en is misschien gangbaarder, maargoed als er maar iets is dat velden scheidt, niet?

[Wolfboy]

T-MOB schreef op dinsdag 17 mei 2005 @ 14:03:
[...]

offtopic:
A:Omdat OpenOffice.org ze anders opent met de writer ipv de spreadsheet..

Q:Waarom maak je ze dan niet gewoon comma-seperated?
A:Omdat de | veel makkelijker verwerkt in een applicatie, hoef je niets voor te escapen etc. TAB als seperator zou ook kunnen en is misschien gangbaarder, maargoed als er maar iets is dat velden scheidt, niet?

Ok

Enne, ik heb het eventjes op de testserver op m'n werk getest maar daar is er nauwelijks verschil merkbaar tussen inner en left joins.
Bij MySQL 4.0.24 @ Gentoo Linux is het verschil iig niet noemenswaardig.

20 id's
inner-inner: 0,0178s (136 rows)
left-left: 0,0182s (136 rows)

Ik maak straks nog wel een wat uitgebreidere test.

[T-MOB]

Wolfboy schreef op dinsdag 17 mei 2005 @ 14:18:
[...]
Ok

Enne, ik heb het eventjes op de testserver op m'n werk getest maar daar is er nauwelijks verschil merkbaar tussen inner en left joins.
Bij MySQL 4.0.24 @ Gentoo Linux is het verschil iig niet noemenswaardig.

20 id's
inner-inner: 0,0178s (136 rows)
left-left: 0,0182s (136 rows)

Ik maak straks nog wel een wat uitgebreidere test.

Jup, blijkbaar is er wat veranderd sinds MySQL 3. Als ik een explain run op de inner-inner query in 3.23.53-max-nt op win2k dan ziet het plaatje er zo uit:


Een explain op de left-left query resulteert in het volgende:


Met MySQL 4.1.11-Debian_1-log op een (d^h) Debian doos laten beide queries hetzelfde plaatje zien:


MySQL 4 pakt dus voor beide queries dezelfde strategie. Wat me nog opvalt is dat de rows kolom in de explains laat zien dat MySQL 3 blijkbaar aan de wandel gaat met 313 rijen terwijl MySQL 4 er 322 nodig heeft. Voor de rest is de explain voor de inner-inner query exact gelijk.
Voor mensen die ook willen spelen met hun verzameling webservers een nieuw zipje met een toegevoegd een scriptje om een testdatabase te bouwen: clickez!

[Wolfboy]

Ik heb nog eventjes verder getest maar ik kom bij ongeveer alle mogelijkheden op ongeveer dezelfde resultaten uit.

Blijkbaar is het dus bij MySQL 4 opgelost.