[MySQL] Joins > lange querytijden

[Cavorka]

'Avond,

ik krijg net een mailtje van mijn hosting bedrijf dat mijn queries te lang zouden duren (dus >250 seconden!!!).

Niet zo mooi uiteraard, maar voor mij nogal onbegrijpelijk aangezien het hier lokaal om mijn computer allemaal zo snel gaat als het maar dan, je merkt in ieder geval niets van parse tijden is ik mijn website lokaal bekijk.

Anyways, om wat queries hebben zij het dan:

code:

SELECT *
FROM BPData, BPID, BPGroups
    WHERE BPID.BPIDDir = 'honda'
    AND
        BPData.BPID = BPID.BPID
    AND
        BPID.BPIDGroupID = BPGroups.GroupID;

Levert error:
# Time: 040509 13:28:43
# User@Host: *****[****] @ ****.****.**** [**.***.**.***]
# Query_time: 288 Lock_time: 0 Rows_sent: 60 Rows_examined: 7006727

Dit levert ongeveer 30-60 rows op als return, voor al dit soort queries ('honda' is dus van alles, daarop wordt de query gebaseerd, maar het is zeker GEEN zoekopdracht! BPID is dus zoiets als 'honda'.).

Dit zou volgens hun dus mis gaan. Nu is mijn vraag: kan het dat deze dubbele join, of driedubbele join echt van dit soort query tijden oplevert? Wat is jullie ervaring ermee? Kunnen dit soort queries echt zo lang duren?
Ik heb zelf uiteraard dit allemaal heel veel getest, lokaal, en ook op de server bij hun (beide via PHPMyAdmin en dan duurt zo'n soort query ongeveer 0.0005 seconde...). Thanks voor alle info.

PS: Ik heb namelijk het idee dat zij aan het prutsen zijn met hun verbinding tussen hun MySQL en HTTP servers.

Tabelstructuur (eerste mysql tabel die ik maakte).
BPData (3200 rows):

code:

Field   Type        
BPNr      smallint(4)
BPID      tinyint(3)
BPTitle       varchar(50)
BPFile  varchar(50)
BPLinkID     varchar(20)
BPResX  smallint(4)
BPResY  smallint(4)
BPSize  smallint(3)
BPViewF   enum('1','0')
BPViewT   enum('1','0')
BPViewR   enum('1','0')
BPViewS   enum('1','0')
BPUpdateID smallint(3)

BPID (160 rows):

code:

Field       Type
BPID                      tinyint(3)
BPIDGroupID   tinyint(4)
BPIDTitle     varchar(30)
BPIDDir       varchar(30)

BPGroups (12 rows):

code:

Field       Type
GroupID         tinyint(4)
GroupName    varchar(20)
GroupDir       varchar(20)

[ Voor 38% gewijzigd door Cavorka op 10-05-2004 01:06 ]

[MatHack]

Welke velden zijn je Primary Key's en heb je ze ook als zodanig gedefineerd?
Heb je op je Foreign Key's ook indexes staan?

[igmar]

Ten eerste : Welke versie van MySQL hebben we het over ? 3.xx heeft problemen met het optimaliseren van joins, en bij bepaalde situaties wordt er een temp table op disk aangemaakt, en dat is inderdaad merkbaar trager.

Indien het resultset van de join > tmp_table_size is wordt er een tijdelijke tabel op disk aangemaakt. Bij bepaalde WHERE clauses gebeurt dat altijd. EXPLAIN geeft over het algemeen nuttige info, en er is ook een stuk over in de MySQL manual, die ik zo snel even niet meer terug kon vinden.

[Creepy]

Cavorka schreef op 10 mei 2004 @ 01:01:
Dit zou volgens hun dus mis gaan. Nu is mijn vraag: kan het dat deze dubbele join, of driedubbele join echt van dit soort query tijden oplevert? Wat is jullie ervaring ermee? Kunnen dit soort queries echt zo lang duren?
Ik heb zelf uiteraard dit allemaal heel veel getest, lokaal, en ook op de server bij hun (beide via PHPMyAdmin en dan duurt zo'n soort query ongeveer 0.0005 seconde...). Thanks voor alle info.

PS: Ik heb namelijk het idee dat zij aan het prutsen zijn met hun verbinding tussen hun MySQL en HTTP servers.

Afhankelijk van de load op de servers en het aantal rows (7006727 is best een redelijk aantal) kan het inderdaad zo lang duren. Neemt niet weg dat het ontzettend traag is.

Tabelstructuur (eerste mysql tabel die ik maakte).
BPData (3200 rows):

code:

Field   Type        
BPNr      smallint(4)
BPID      tinyint(3)
BPTitle       varchar(50)
BPFile  varchar(50)
BPLinkID     varchar(20)
BPResX  smallint(4)
BPResY  smallint(4)
BPSize  smallint(3)
BPViewF   enum('1','0')
BPViewT   enum('1','0')
BPViewR   enum('1','0')
BPViewS   enum('1','0')
BPUpdateID smallint(3)

BPID (160 rows):

code:

Field       Type
BPID                      tinyint(3)
BPIDGroupID   tinyint(4)
BPIDTitle     varchar(30)
BPIDDir       varchar(30)

BPGroups (12 rows):

code:

Field       Type
GroupID         tinyint(4)
GroupName    varchar(20)
GroupDir       varchar(20)

Leuk zo'n tabel structuur. Kun je ook aangeven welke velden PK's zijn, en welke indexen er zijn gedefinieerd?

Gezien je query tijden en het aantal bekeken rows gok ik dat je helemaal geen indexen hebt. Gezien je velden in je query zou ik op elke tabel 1 index maken met hierin alle gebruikte velden in de where.

[ Voor 5% gewijzigd door Creepy op 10-05-2004 09:01 ]

[Verwijderd]

Cavorka schreef op 10 mei 2004 @ 01:01:

code:

SELECT *
FROM BPData, BPID, BPGroups
    WHERE BPID.BPIDDir = 'honda'
    AND
        BPData.BPID = BPID.BPID
    AND
        BPID.BPIDGroupID = BPGroups.GroupID;

- Controleer de performance van de query door na elke wijziging in je query "EXPLAIN SELECT * ....." uit te voeren.
- Zijn alle velden in het resultaat nodig? Zo nee, wijzig dan de SELECT * in een zinnige rij kolomnamen
- Zet in een JOIN de meest beperkte tabel vooraan
- Zet ook eens (ik weet dat de MySQL manual anders aanraadt) de beperkende condities uit de WHERE clause in de JOIN condities:

code:

SELECT *
FROM BPID 
JOIN BPData ON BPID.BPIDDir = 'honda'
               AND BPData.BPID = BPID.BPID
JOIN BPGroups ON BPID.BPIDGroupID = BPGroups.GroupID;

of

code:

SELECT *
FROM BPID 
JOIN BPGroups ON BPID.BPIDDir = 'honda'
               AND BPID.BPIDGroupID = BPGroups.GroupID
JOIN BPData ON BPData.BPID = BPID.BPID;

Tenslotte, wat thuis op 3200 records vooruitvliegt, kan elders met +7.000.000 records gaan kruipen.

[Cavorka]

MatHack schreef op 10 mei 2004 @ 07:30:
Welke velden zijn je Primary Key's en heb je ze ook als zodanig gedefineerd?
Heb je op je Foreign Key's ook indexes staan?

Ik heb geen primary keys, ook geen foreign keys. Kennelijk is dit essentieel aangezien iedereen hiermee komt. * Cavorka zal zich maar gaan inlezen, ik had namelijk geen idee dat dat zo belangrijk was.

igmar schreef op 10 mei 2004 @ 08:52:
Ten eerste : Welke versie van MySQL hebben we het over ? 3.xx heeft problemen met het optimaliseren van joins, en bij bepaalde situaties wordt er een temp table op disk aangemaakt, en dat is inderdaad merkbaar trager.

4.0.15 (bij mij thuis en bij de hosting)

Indien het resultset van de join > tmp_table_size is wordt er een tijdelijke tabel op disk aangemaakt. Bij bepaalde WHERE clauses gebeurt dat altijd. EXPLAIN geeft over het algemeen nuttige info, en er is ook een stuk over in de MySQL manual, die ik zo snel even niet meer terug kon vinden.

Alright, ik zal dit even gaan testen. Thanks.

Creepy schreef op 10 mei 2004 @ 08:52:
[...]Afhankelijk van de load op de servers en het aantal rows (7006727 is best een redelijk aantal) kan het inderdaad zo lang duren. Neemt niet weg dat het ontzettend traag is.

[...]
Leuk zo'n tabel structuur. Kun je ook aangeven welke velden PK's zijn, en welke indexen er zijn gedefinieerd?

Geen. Alleen een fulltext over BPTitle voor zoekdoeleinden.

Gezien je query tijden en het aantal bekeken rows gok ik dat je helemaal geen indexen hebt. Gezien je velden in je query zou ik op elke tabel 1 index maken met hierin alle gebruikte velden in de where.

Dat is een juiste gok. Thanks voor de tip, daar ga ik mee aan de slag.

Verwijderd schreef op 10 mei 2004 @ 09:37:
- Controleer de performance van de query door na elke wijziging in je query "EXPLAIN SELECT * ....." uit te voeren.
- Zijn alle velden in het resultaat nodig? Zo nee, wijzig dan de SELECT * in een zinnige rij kolomnamen
Dit heb ik gedaan, en getest, maakte niets uit, maar zal het toch maar doen voor de netheid
- Zet in een JOIN de meest beperkte tabel vooraan
Will do.
- Zet ook eens (ik weet dat de MySQL manual anders aanraadt) de beperkende condities uit de WHERE clause in de JOIN condities:

code:

SELECT *
FROM BPID 
JOIN BPData ON BPID.BPIDDir = 'honda'
               AND BPData.BPID = BPID.BPID
JOIN BPGroups ON BPID.BPIDGroupID = BPGroups.GroupID;

of

code:

SELECT *
FROM BPID 
JOIN BPGroups ON BPID.BPIDDir = 'honda'
               AND BPID.BPIDGroupID = BPGroups.GroupID
JOIN BPData ON BPData.BPID = BPID.BPID;

Tenslotte, wat thuis op 3200 records vooruitvliegt, kan elders met +7.000.000 records gaan kruipen.

Als gezegd: ik heb die queries bij mij, en bij de hosting getest, duurde bij hun zelfs korter dan bij mij thuis.

Hartelijk dank voor alle tips, ik ga kijken of het helpt!

-----------------------------------

Okay, ik heb net wat indices en primary keys aangelegd.
BPData:

code:

Field   Type        Key
BPNr    smallint(4) PRI
BPID    tinyint(3) MUL
BPTitle varchar(50) MUL
BPFile  varchar(50) 
BPLinkID varchar(20)
BPResX  smallint(4)
BPResY  smallint(4)
BPSize  smallint(3)
BPViewF enum('1','0')
BPViewT enum('1','0')
BPViewR enum('1','0')
BPViewS enum('1','0')
BPUpdateID  smallint(3)

BPID:

code:

Field       Type        Key
BPID        tinyint(3)  PRI
BPIDGroupID tinyint(4)  MUL
BPIDTitle   varchar(30) 
BPIDDir     varchar(30) MUL

BPGroups:

code:

Field       Type        Key
GroupID     tinyint(4)  PRI
GroupName   varchar(20) 
GroupDir    varchar(20) MUL

[ Voor 19% gewijzigd door Cavorka op 10-05-2004 13:38 ]

[ripexx]

Cavorka schreef op 10 mei 2004 @ 13:10:
Ik heb geen primary keys, ook geen foreign keys. Kennelijk is dit essentieel aangezien iedereen hiermee komt, maar ik heb geen idee waar ze voor dienen. * Cavorka zal zich maar gaan inlezen, ik had namelijk geen idee dat dat zo belangrijk was.

Hier zit je eerste en waarschijnlijk ook het de grootste fout/ bottleneck. Zie ook de faq van P&W P&W FAQ - SQL

[curry684]

Cavorka schreef op 10 mei 2004 @ 13:10:
[...]

Ik heb geen primary keys, ook geen foreign keys. Kennelijk is dit essentieel aangezien iedereen hiermee komt. * Cavorka zal zich maar gaan inlezen, ik had namelijk geen idee dat dat zo belangrijk was.

Ahum, zonder indexen heb je gewoon een luxe versie van commaseparated files ja, en dat schiet niet echt op

Een index is een 'quick lookup' door je tabellen. Zonder index kan een join simpelweg niet 'slim' uitgevoerd worden, omdat de database iedere keer een full table scan moet doen om de waardes te vinden waarop je joint. Met indexen op de join condities (ergo vooral primary en foreign keys) kan ie puur op z'n indexen besluiten wat ie nodig heeft, en dan pas voor de 'fysieke' join gericht de database zelf induiken. Dit kan je een niet kinderachtige factor 100 of meer op snelheid schelen zodra je enigszins in de data begint te lopen

edit:
omg, zie net pas dat je 7006727 rows hebt.... dan heb je nog geluk als het maar een factor 100 scheelt of je dat indexeert

[ Voor 8% gewijzigd door curry684 op 10-05-2004 13:27 ]

[Cavorka]

ripexx schreef op 10 mei 2004 @ 13:14:
[...]

Hier zit je eerste en waarschijnlijk ook het de grootste fout/ bottleneck. Zie ook de faq van P&W P&W FAQ - SQL

Dank!

curry684 schreef op 10 mei 2004 @ 13:26:
[...]

Ahum, zonder indexen heb je gewoon een luxe versie van commaseparated files ja, en dat schiet niet echt op

Een index is een 'quick lookup' door je tabellen. Zonder index kan een join simpelweg niet 'slim' uitgevoerd worden, omdat de database iedere keer een full table scan moet doen om de waardes te vinden waarop je joint. Met indexen op de join condities (ergo vooral primary en foreign keys) kan ie puur op z'n indexen besluiten wat ie nodig heeft, en dan pas voor de 'fysieke' join gericht de database zelf induiken. Dit kan je een niet kinderachtige factor 100 of meer op snelheid schelen zodra je enigszins in de data begint te lopen

edit:
omg, zie net pas dat je 7006727 rows hebt.... dan heb je nog geluk als het maar een factor 100 scheelt of je dat indexeert

Ik heb net dus al die indices en keys aangebracht, dit scheelde inderdaad een factor 50. ECHTER, de querie duurde dus, bij mij dan, 0.005 seconde... nu 0.0001. Dat is inderdaad een lekkere verbetering, maarja, echt boeiend is het niet: lokaal zal ik er niets van merken. Hopelijk zal dit wel het probleem zijn bij de hosting, moet eigenlijk wel als ik het zo hoor.

Tsja, die 7.10⁶ rows zijn 3200*160*12, de triple join.

Iedereen nogmaals dank voor deze wijze les in databases knowledge.

[ Voor 10% gewijzigd door Cavorka op 10-05-2004 13:41 ]

[curry684]

Cavorka schreef op 10 mei 2004 @ 13:36:
[...]

Ik heb net dus al die indices en keys aangebracht, dit scheelde inderdaad een factor 50. ECHTER, de querie duurde dus, bij mij dan, 0.005 seconde... nu 0.0001. Dat is inderdaad een lekkere verbetering, maarja, echt boeiend is het niet: lokaal zal ik er niets van merken. Hopelijk zal dit wel het probleem zijn bij de hosting, moet eigenlijk wel als ik het zo hoor.

Als je d'r feitelijk mail over krijgt moet je niet vreemd opkijken als ie bij hun van 30 seconden naar 0.3 seconden gaat op een verder flink belaste bak