[mysql] index vraagjes bij een join

[marty]

Ik join 8 tabellen en heb overal indexen op staan, maar nou geeft EXPLAIN aan dat ie bij 1 tabel 10 rijen moet bekijken en ik snap niet waarom.
Toevallig is dat een tabel waar ik op twee kolommen join,

code:

LEFT JOIN tabel8 AS t8 ON t8.isn = t1.isn AND t8.bid = t1.bid

maar ook als ik er maar 1 specificeer zegt ie nog steeds 10 kolommen nodig te hebben.
Vraag 1:
- hoe kan ik er achter komen waarom ie er 10 denkt nodig te hebben?

Hij geeft bij possbile keys 2 mogelijkheden aan (dat klopt, want ik wil allebei gebruiken om te joinen), maar bij key staat er dan maar eentje.
Vraag 2:
Wat doet ie met die ander?

[whoami]

Niet echt een antwoord op uw vraag, maar overal indexen op hebben kan de performance schaden. (Niet bij een select query, maar wel bij insert/updates/deletes. De indexen moeten dan nl. ook bijgewerkt worden).
Het is beter dat je enkel indexen legt op de kolommen waarop je op joint, sorteert en filtert.

Wat je ook kunt doen is de volgorde van je where statements eens veranderen en dan eens kijken wat die explain zegt.

[Infinitive]

Hoeveel informatie heb je in de tabel zitten? Als er nogal weinig records zijn, dan worden indexen nog wel eens overgeslagen omdat een filescan dan efficiënter is.

Ik ben overigens de semantiek van LEFT JOIN al lang weer vergeten, dus ik weet niet zeker wat het met NULL-velden doet waar je op joined, maar als deze ook in het resultaat erbij moeten komen, dan zal dit ook een reden kunnen zijn waarom er (naar verwachting) 10 records afgezocht kunnen worden. Hetzelfde verhaal geld ook als de velden waarop je joined niet uniek zijn.

[ Voor 56% gewijzigd door Infinitive op 23-02-2003 10:45 ]

[marty]

whoami schreef op 23 February 2003 @ 09:28:
Niet echt een antwoord op uw vraag, maar overal indexen op hebben kan de performance schaden. (Niet bij een select query, maar wel bij insert/updates/deletes. De indexen moeten dan nl. ook bijgewerkt worden).
Het is beter dat je enkel indexen legt op de kolommen waarop je op joint, sorteert en filtert.

Dat weet ik en dat heb ik ook echt niet gedaan. Ik heb alleen een index gezet op de kolommen die ik regelmatig in JOINS gebruik.
De volgorde maakt overigens niets uit. (heb ik geprobeerd)

Infinitive schreef op 23 February 2003 @ 10:41:
Hoeveel informatie heb je in de tabel zitten? Als er nogal weinig records zijn, dan worden indexen nog wel eens overgeslagen omdat een filescan dan efficiënter is.

Ca 4000 records per tabel.

Ik ben overigens de semantiek van LEFT JOIN al lang weer vergeten, dus ik weet niet zeker wat het met NULL-velden doet waar je op joined, maar als deze ook in het resultaat erbij moeten komen, dan zal dit ook een reden kunnen zijn waarom er (naar verwachting) 10 records afgezocht kunnen worden. Hetzelfde verhaal geld ook als de velden waarop je joined niet uniek zijn.

Ja, NULL velden worden meegenomen met een LEFT JOIN; in een WHERE worden ze er uit gelaten (vandaar mijn keuze voor LEFT JOIN). In die kolom waar ie er 10 denkt nodig te hebben zijn de kolommen inderdaad niet uniek, maar dit geldt ook voor een andere tabel en daarvan zegt ie wel dat ie maar 1 row hoeft te bekijken. Ik snap de logica dus niet zo goed

[ Voor 4% gewijzigd door marty op 23-02-2003 11:33 ]

[Infinitive]

Ja, NULL velden worden meegenomen met een LEFT JOIN; in een WHERE worden ze er uit gelaten (vandaar mijn keuze voor LEFT JOIN). In die kolom waar ie er 10 denkt nodig te hebben zijn de kolommen inderdaad niet uniek, maar dit geldt ook voor een andere tabel en daarvan zegt ie wel dat ie maar 1 row hoeft te bekijken. Ik snap de logica dus niet zo goed

De indexen kunnen naast de 'index' ook nog wat statistieken bijhouden, zoals bijvoorbeeld de verdeling over unieke waarden. Dus als het weet dat voor een bepaalde waarde maar een record is, dan hoeven er ook niet meer records bekeken te worden. Merk wel op: dit is een schatting die de optimizer maakt.

Ik zou overigens niet wakker liggen als 'ie voor die query 10 records zal moeten bekijken, daar merk je wat performance betreft toch niets van... tenzij het records van een terabyte zijn natuurlijk

[ Voor 19% gewijzigd door Infinitive op 23-02-2003 16:03 ]

[marty]

Infinitive schreef op 23 February 2003 @ 16:01:
[...]Ik zou overigens niet wakker liggen als 'ie voor die query 10 records zal moeten bekijken, daar merk je wat performance betreft toch niets van... tenzij het records van een terabyte zijn natuurlijk

Nou, 4.000 of 40.000 records bekijken vind ik nogal een verschil, zeker gezien het feit dat ik 'm in totaal zo'n 35 kolommen laat selecteren.
En m'n query duurt nu zo'n 13 seconde en dat vind ik toch wat aan de trage kant, dus ik zou daar graag nog wat van afhalen

[Infinitive]

Toevallig is dat een tabel waar ik op twee kolommen join,

Heb je er ook aan gedacht om een index over beide kolommen te plaatsen? Als je de index alleen op losse kolommen hebt zitten, dan zullen er denk ik toch (een klein aantal) records zelf gescand moeten worden. Wanneer je een index echter over beide kolommen hebt zitten, dan kan hoeft alleen die index zelf gebruikt worden.

[ Voor 5% gewijzigd door Infinitive op 23-02-2003 21:26 ]

[marty]

hmm...dat heb ik ff geprobeerd, maar heeft niet echt voordelige gevolgen
hij geeft in de explain wel aan dat ie nu maar 1 rij nodigt denkt te hebben, maar m'n query duurt bijna 2x zo lang. 24sec ipv 13sec.

Is dat normaal voor een query over 8 tabellen waarbij je ca 4000 records per tabel heb? Ik vind het zo traag....

[Infinitive]

hmm...dat heb ik ff geprobeerd, maar heeft niet echt voordelige gevolgen
hij geeft in de explain wel aan dat ie nu maar 1 rij nodigt denkt te hebben, maar m'n query duurt bijna 2x zo lang. 24sec ipv 13sec.

Mmm dat is wel vreemd. Zou al die extra tijd komen doordat er extra indexen afgelopen moeten worden?

Maar goed, ik zou ook niets verder meer weten. Heb je al eens een andere database pakket geprobeerd? Je zou voor de vergelijking je database in access kunnen importeren en kijken hoe lang je query er dan over doet...

[marty]

nee, als possible keys geeft ie telkens maar 1 mogelijkheid en dat is ook de goede, daar heb ik al naar gekeken.

ja, een ander database pakket is misschien niet zo'n slecht idee. access lijkt me niet echt slim, want die gaat standaard al over z'n nek als je boven de 5000 records komt. Maar ik zit er al langer over na te denken om misschien op postgresql over te gaan. zitten toch wel redelijk wat beperkingen aan mysql. kan ik het daar gelijk eens mee uit proberen

[ACM]

Mysql is niet echt een held in joins van "veel" tables, bij mijn weten. Hoewel 13 / 24 seconden wel vrij erg is.
Kan je eens de tabel beschrijvingen posten en de indices daarop?

En dan uiteraard ook je query (of een voorbeeld) en de explain ervan?

[marty]

Okay, you asked for it:

overzicht van de tabellen
CREATE TABLE bedrijven (
unique_id bigint(20) unsigned NOT NULL auto_increment,
id bigint(20) unsigned NOT NULL default '0',
aid bigint(20) unsigned NOT NULL default '0',
cid bigint(20) unsigned NOT NULL default '0',
bedrijfsnaam varchar(100) NOT NULL default '',
vestiging varchar(15) default NULL,
verzendcode longtext,
DCode int(250) default NULL,
PRIMARY KEY (unique_id),
KEY ids (id,aid,cid)
) TYPE=MyISAM;

CREATE TABLE bedrijven_adres (
u_adres_id bigint(20) unsigned NOT NULL auto_increment,
bid bigint(20) unsigned NOT NULL default '0',
adres varchar(50) default NULL,
postcode varchar( default NULL,
plaats varchar(50) default NULL,
tfnrvast varchar(13) default NULL,
tfnrmobiel varchar(13) default NULL,
tfnrfax varchar(13) default NULL,
email varchar(50) default NULL,
homepage varchar(100) default NULL,
PRIMARY KEY (u_adres_id),
KEY adres_bid (bid)
) TYPE=MyISAM;

CREATE TABLE bedrijven_contact (
u_contact_id bigint(20) unsigned NOT NULL auto_increment,
bid bigint(20) unsigned NOT NULL default '0',
sex enum('m','v') default NULL,
contactpersoon varchar(30) default NULL,
voorletters varchar(10) default NULL,
voornaam varchar(20) default NULL,
achternaam varchar(30) default NULL,
titel varchar(5) default NULL,
aanspreektitel enum('Geachte heer','Geachte mevrouw','Weledelzeergeleerde Heer','Weledelzeergeleerde Vrouwe','Weledelgestrenge Heer','Weledelgestrenge Vrouwe') default NULL,
functie_contactpersoon varchar(30) default NULL,
tfnr_contactpersoon varchar(13) default NULL,
c_email varchar(60) default NULL,
andere_contactpersoon varchar(40) default NULL,
andere_sex enum('m','v') default NULL,
andere_functie varchar(30) default NULL,
andere_tfnr varchar(13) default NULL,
PRIMARY KEY (u_contact_id),
KEY contact_bid (bid)
) TYPE=MyISAM;

CREATE TABLE data_main (
id bigint(20) NOT NULL auto_increment,
sex varchar(255) default NULL,
voorletters varchar(12) default NULL,
voornaam varchar(50) default NULL,
achternaam varchar(50) default NULL,
adres varchar(100) default NULL,
postcode varchar(7) default NULL,
woonplaats varchar(60) default NULL,
tfnr_vast varchar(20) default NULL,
tfnr_mobiel varchar(20) default NULL,
tfnr_fax varchar(20) default NULL,
email varchar(100) default NULL,
dob datetime default NULL,
geboortedatum varchar( default NULL,
geboorteplaats varchar(80) default NULL,
nationaliteit varchar(5) default NULL,
burgelijke_staat enum('Alleenstaand','Gescheiden','Gehuwd','Ongehuwd','Samenwonend','Weduwnaar/Weduwe') default NULL,
regio enum('''t Gooi','Flevoland','Randstad-Noord','Randstad-Zuid','Utrecht','Zuid-Holland','Oost-Nederland','Brabant','Kop van N-Holland','Limburg','Noord-Nederland','Amsterdam','Zeeland','Drente') default NULL,
status varchar(30) default NULL,
soort_werk varchar(50) default NULL,
ligauto varchar(15) default NULL,
vestiging varchar(10) default NULL,
afdeling varchar(50) default NULL,
aantal_uren float default NULL,
uren_pw_min tinyint(4) default NULL,
uren_pw_max tinyint(3) unsigned default NULL,
vijfenzestig enum('on') default NULL,
werkervaring text,
opleiding text,
specifiek text,
zoek_flex enum('on') default NULL,
zoek_vast enum('on') default NULL,
zoek_project enum('on') default NULL,
salaris_indicatie varchar(50) default NULL,
huidig_inkomen varchar(40) default NULL,
rijbewijs_soort varchar(5) default NULL,
rijbewijs_nr varchar(25) default NULL,
reizen_max int(11) unsigned default NULL,
reizen_keuze char(3) default NULL,
hobbies mediumtext,
intake_bijzonderheden text,
behandeld_door varchar(40) default NULL,
datum_intake varchar(15) default NULL,
online enum('(online-inschrijving)') default NULL,
aandachtspunten mediumtext,
PRIMARY KEY (id)
) TYPE=MyISAM;

CREATE TABLE declaratie (
id bigint(20) unsigned NOT NULL auto_increment,
inschrijfnummer bigint(20) NOT NULL default '0',
declaratienummer int(11) NOT NULL default '9999',
datum datetime default NULL,
weeknummer tinyint(2) NOT NULL default '0',
jaar varchar(4) NOT NULL default '0000',
debiteuren_nummer int(250) default NULL,
bijzonderheden tinytext,
bedrijfs_id bigint(20) default NULL,
vestiging varchar(15) default NULL,
ingevoerd_door varchar(100) default NULL,
printed enum('','true') NOT NULL default '',
PRIMARY KEY (id),
KEY bedrijfs_id (bedrijfs_id),
KEY isn (inschrijfnummer)
) TYPE=MyISAM;

CREATE TABLE declaratie_uren (
declaratie_id bigint(20) unsigned NOT NULL default '0',
uren float default NULL,
verlof float default NULL,
ziek float default NULL,
dag enum('maandag','dinsdag','woensdag','donderdag','vrijdag','zaterdag','zondag') default NULL,
t1_toeslag float default NULL,
t1_uren float default NULL,
t2_toeslag float default NULL,
t2_uren float default NULL,
t3_toeslag float default NULL,
t3_uren float default NULL,
t4_toeslag float default NULL,
t4_uren float default NULL,
uren_per_dag float default NULL,
reiskosten float default NULL,
overige_kosten float default NULL,
KEY did (declaratie_id)
) TYPE=MyISAM;

CREATE TABLE personalia (
id int(10) unsigned NOT NULL auto_increment,
inschrijfnummer bigint(20) default NULL,
ArbVerg varchar(50) default NULL,
kopie varchar(10) default NULL,
paspoortnr varchar(50) default NULL,
kopie_paspoort enum('','on') default NULL,
paspoort_geldig_tot varchar( default NULL,
sofinr varchar(50) default NULL,
loonbelastinggroep varchar(50) default NULL,
bankgironr varchar(50) default NULL,
zkv_naam varchar(50) default NULL,
zkv_soort varchar(50) default NULL,
zkv_registratienr varchar(50) default NULL,
zkv_adres varchar(50) default NULL,
zkv_postcode varchar(50) default NULL,
zkv_plaats varchar(50) default NULL,
PRIMARY KEY (id),
KEY isn (inschrijfnummer)
) TYPE=MyISAM;

CREATE TABLE werkgegevens (
inschrijfnummer bigint(20) default NULL,
werkgever_no tinyint(3) unsigned default NULL,
Bedrijf varchar(255) default NULL,
bedrijfsid bigint(20) unsigned default NULL,
Bankrekening varchar(255) default NULL,
Sofinummer varchar(255) default NULL,
Ziekenfonds varchar(255) default NULL,
Arbeidsvergunningsnummer varchar(255) default NULL,
clienten_nummer varchar(255) default NULL,
functie varchar(255) default NULL,
aanvangsdatum varchar(255) default NULL,
einddatum varchar(10) default NULL,
eind varchar( default NULL,
week tinyint(3) unsigned default NULL,
werktijden varchar(255) default NULL,
indicatie_uren_pw tinyint(3) unsigned default NULL,
bruto_uurloon varchar(20) default NULL,
vorig_uurloon varchar(20) default NULL,
reiskosten varchar(255) default NULL,
overige_kosten varchar(255) default NULL,
toeslagen varchar(255) default NULL,
duur_overeenkomst varchar(255) default NULL,
vijfzesplus varchar(20) default NULL,
intercedent varchar(255) default NULL,
verdeelsleutel varchar(20) default NULL,
van varchar(20) default NULL,
tot varchar(20) default NULL,
winstmarge float default '1',
aansluitnr varchar(50) default NULL,
bijzonderheden varchar(50) default NULL,
KEY wisn (inschrijfnummer),
KEY wbid (bedrijfsid)
) TYPE=MyISAM;

Er kunnen hier en daar nog wat rare dingen in staan. Het zijn oorspronkelijk access tabellen van iemand anders geweest die ik om heb gezet. Soms heb ik nog wat oude kolommen laten staan om te kijken wat daar in stond.

de query

code:

SELECT
    d.id AS did, d.*,
    dm.sex, CONCAT(dm.voornaam,' ', dm.achternaam) AS naam, dm.adres, dm.postcode, dm.woonplaats, dm.tfnr_vast, dm.dob, dm.geboortedatum, dm.nationaliteit,
    personalia.*,
    b.bedrijfsnaam,
  ba.adres, ba.postcode, ba.plaats, ba.tfnrvast, bc.voorletters, bc.voornaam, bc.achternaam,
    w.bruto_uurloon, w.vorig_uurloon, w.reiskosten, w.overige_kosten,
    SUM(du.uren) AS uren, SUM(verlof) AS verlof, SUM(ziek) AS ziek
FROM declaratie AS d
    LEFT JOIN data_main AS dm ON dm.id = d.inschrijfnummer
    LEFT JOIN personalia ON personalia.inschrijfnummer = d.inschrijfnummer
    LEFT JOIN declaratie_uren AS du ON du.declaratie_id = d.id
    LEFT JOIN bedrijven AS b ON b.unique_id = d.bedrijfs_id
    LEFT JOIN werkgegevens AS w ON w.inschrijfnummer = d.inschrijfnummer AND w.bedrijfsid = d.bedrijfs_id
    LEFT JOIN bedrijven_adres AS ba ON b.aid = ba.u_adres_id
    LEFT JOIN bedrijven_contact AS bc ON b.cid = bc.u_contact_id
WHERE d.printed != 'true'
GROUP BY d.id
LIMIT 0,1

en de explain

code:

table       type    possible_keys   key key_len ref         rows    Extra
d       ALL NULL        NULL    NULL    NULL            4395    where used; Using temporary
dm      eq_ref  PRIMARY     PRIMARY 8   d.inschrijfnummer   1   
personalia  ref isn     isn 9   d.inschrijfnummer   1   
du      ref did     did 8   d.id            1   
b       eq_ref  PRIMARY     PRIMARY 8   d.bedrijfs_id       1   
w       ref wisn,wbid   wisn    9   d.inschrijfnummer   1   
ba      eq_ref  PRIMARY     PRIMARY 8   b.aid           1   
bc      eq_ref  PRIMARY     PRIMARY 8   b.cid           1

[ACM]

Wat is trouwens de globale verhouding tussen beide waarden voor de d.printed ?
Zijn er heel veel waarvoor geldt dat ze "!= 'true'" zijn of juist relatief weinig?

Als het er relatief weinig zijn zou ik daar ook nog een index op plaatsen, zoals je het nu hebt dwing je je database daar iig een tablescan (ipv een indexscan) te doen, hoewel het dus van de verhouding afhangt wat beter is

"LEFT JOIN werkgegevens AS w ON w.inschrijfnummer = d.inschrijfnummer AND w.bedrijfsid = d.bedrijfs_id"
Die had je al getest met een twee-kolom's index toch?

Verder zie ik zo gauw ook geen verbeteringen.
Laat deze "SUM(du.uren) AS uren, SUM(verlof) AS verlof, SUM(ziek) AS ziek" rij es weg en dan ook de GROUP BY. Scheelt dat veel in tijd?

Waarom doe je trouwens een limit 0,1 weet je zeker dat er maar 1 resultaat uit komt?

Evt kan je nog steeds een tabel-join bij te voegen, beginnend met enkel je select op de declaratie om uit te zoeken of de tijd ergens specifiek in zit (bijv als je een tabel toevoegd dat de tijd omhoog schiet, terwijl het bij andere toevoegingen maar een klein stukje omhoog gaat)

[ Voor 15% gewijzigd door ACM op 24-02-2003 01:10 ]

[marty]

ACM schreef op 24 February 2003 @ 01:04:
Wat is trouwens de globale verhouding tussen beide waarden voor de d.printed ?
Zijn er heel veel waarvoor geldt dat ze "!= 'true'" zijn of juist relatief weinig?

Als het er relatief weinig zijn zou ik daar ook nog een index op plaatsen, zoals je het nu hebt dwing je je database daar iig een tablescan (ipv een indexscan) te doen, hoewel het dus van de verhouding afhangt wat beter is

Het zullen er per keer een stuk of 100 zijn denk ik. Het idee erachter is, dat die uren verwerkt worden en uitgeprint. Op het moment dat dat is gebeurd zet ik die waarde op true, zodat ik weet dat ze zijn verwerkt en ze dus niet meer uitgeprint moeten worden (of mee gekloot)

"LEFT JOIN werkgegevens AS w ON w.inschrijfnummer = d.inschrijfnummer AND w.bedrijfsid = d.bedrijfs_id"
Die had je al getest met een twee-kolom's index toch?

Jup, heb ik getest. Toen ging ie van 13 naar 24 seconde

Verder zie ik zo gauw ook geen verbeteringen.
Laat deze "SUM(du.uren) AS uren, SUM(verlof) AS verlof, SUM(ziek) AS ziek" rij es weg en dan ook de GROUP BY. Scheelt dat veel in tijd?

Dan gaat ie van 13 naar 9 seconde. Scheelt dus wel iets.
Heb ook nog geprobeerd om die ba en bc weg te laten, aangezien ik die niet op declaratie join, maar op de bedrijventabel (Ik had eerder al de fout gemaakt dat ik de bedrijfs_id uit de werkgegevenstabel niet op het bedrijfsid uit de declaratietabel joinde, maar op de bedrijven tabel zelf. Toen was ie 127 seconde....kennelijk vind mysql dat niet zo leuk) maar dat scheelt ook niets

Waarom doe je trouwens een limit 0,1 weet je zeker dat er maar 1 resultaat uit komt?

oh...dat was alleen omdat ik er nog mee aan het testen ben, anders moet ik telkens de data van 4300+ records over het lijntje binnenhalen. Er is namelijk nog niets uitgeprint :-)

Evt kan je nog steeds een tabel-join bij te voegen, beginnend met enkel je select op de declaratie om uit te zoeken of de tijd ergens specifiek in zit (bijv als je een tabel toevoegd dat de tijd omhoog schiet, terwijl het bij andere toevoegingen maar een klein stukje omhoog gaat)

Goeie, ga ik ff doen....

[ Voor 10% gewijzigd door marty op 24-02-2003 01:18 ]

[marty]

Hmm...vaag, ik weet niet wat ik net nou verkeerd heb gedaan toen ik je advies over die SUM en GROUP BY weglaten probeerde op te volgend. ben vergeten de GROUP BY weg te halen denk ik.
Ik ben nu nog eens gaan kloten - ben ff onder de shell ingelogd voor de zekerheid, dat scheelde zowiezo al 2 seonce (nog steeds 11 seconde dus). Toen ik die SUMs weghaalde bleven er van die 11 nog 9 over en toen ik de GROUP BY weghaalde was ie nog maar 0.01 seconde!
Dat is dus de bottleneck. Die GROUP BY....
ben ik lekker mee.
Ik denk dat ik 'm dan maar helemaal weg laat, het uitprinten tot 50 per keer beperk en per record de declaratie_uren tabel apart querie. Dat was ik toch al van plan omdat er ook een overzicht per dag moet komen per record, maar ik niet alles nog eens x7 binnen wilde halen (vandaar de group by...nu gaat het per week). Dat werd wat te veel van het goeie, omdat je dan met heel veel overbodige informatie zit (per week 7x35 kolommen aan dezelfde informatie met alleen een uurtje verschil per row).

p.s. bedankt voor je hulp :-)