[MySQL] Query sneller maken

[DennusB]

Goedemiddag Tweakers,

Ik heb weer eens een issue Ik ben al een heeeeel eind gekomen, maar nu kan ik wel even een beetje hulp gebruiken. Let me explain:
Ik heb 2 tabellen in MySQL. Tabel "T1" & "T2".
In Tabel 1 word elke 24 uur een CSV file van een leverancier ingeladen met productinformatie, zo'n 55.000 records.
In Tabel 2 word elke 24 uur een CSV ingeladen met alle VOORRAADposities van de leverancier. Zo'n 57.000 records.
Dit gaat tot zover prima! Nu wil ik op onze website de live voorraad van de leverancier uitlezen. Met een leuke MySQL query.
Om duidelijk te zijn, dat is deze:

code:

SELECT stock, partnr, omschrijving FROM T1 JOIN T2 ON T1.lev_artnr = T2.sku WHERE T1.partnr = '".$this->artikel["partnummer"]."' LIMIT 0,1

Ook top Werkt. Probleem is alleen dat het lijkt alsof MySQL nogal moeite heeft om die 2 tabellen naast elkaar te leggen. Als er bijvoorbeeld geen match is duurt het uitvoeren van die query bijna 2 seconden! En dat komt de snelheid van de site niet echt ten goede.
Ik ben dus erg benieuwd of er mogelijkheden zijn om die query/databaseindeling sneller te krijgen. Want 2 seconden wachten duurt gewoon te lang.

Ik hoop dat jullie een idee hebben, want ik kan redelijk met MySQL overweg. Maar met "zoveel" data heb ik tot nu toe nog niet gewerkt

Thanks alvast weer

[CodeCaster]

Ik zie een open deur, maar ... explain? Indexen?

[ Voor 56% gewijzigd door CodeCaster op 21-12-2010 15:49 ]

[DennusB]

CodeCaster schreef op dinsdag 21 december 2010 @ 15:48:
Ik zie een open deur, maar ... explain? Indexen?

Ik heb op de kolom "T1.lev_artnr" & "T2.sku" al een index. "Unique". Dat moet genoeg zijn toch?

[justmental]

Ik zou een index op t1.partnr nemen ipv. lev_artnr

[DennusB]

justmental schreef op dinsdag 21 december 2010 @ 15:52:
Ik zou een index op t1.partnr nemen ipv. lev_artnr

Okee. Kan je de reden daarachter even uitleggen? Ik dacht dat de 2 kolommen die vergeleken werden wel het zwaarst zouden zijn.

[frankivo]

als de data maar 1x per 24 uur veranderd, waarom worden deze resultaten dan niet in een cache geplaatst?

[DennusB]

frankivo schreef op dinsdag 21 december 2010 @ 15:55:
als de data maar 1x per 24 uur veranderd, waarom worden deze resultaten dan niet in een cache geplaatst?

Dat kan ook nog Maar omdat het gaat over ~2500 artikelen is dat denk ik toch geen optie.
Want de eerste bezoeker die dan voor het "eerst" dat product bezoekt na een refresh van de data, die kan dik 2/3/4 seconden gaan wachten op de pagina...

[justmental]

DennusB schreef op dinsdag 21 december 2010 @ 15:53:
Okee. Kan je de reden daarachter even uitleggen? Ik dacht dat de 2 kolommen die vergeleken werden wel het zwaarst zouden zijn.

Zoals in de where staat zoek je op partnr, dat kan dan in de index.
Van de gevonden records worden dan de lev_artnr's opgezocht en daarmee wordt in de t2 tabel op de sku's gezocht, ook via een index.

Zonder die eerste index krijg je een full scan op t1.

[Cloud]

DennusB schreef op dinsdag 21 december 2010 @ 15:53:
[...]


Okee. Kan je de reden daarachter even uitleggen? Ik dacht dat de 2 kolommen die vergeleken werden wel het zwaarst zouden zijn.

Je WHERE-clause filtert primair de records uit de resultset, niet de JOIN-clause. Oftewel de kolom T1.partnr moet geindexeerd worden om meer performance te halen. Filteren kost namelijk veel tijd (is wel afhankelijk van datatype).

Om het zoeken van de bijbehorende T2 records snel te houden is de index op de kolommen uit de join natuurlijk ook belangrijk dus die is prima

[ Voor 21% gewijzigd door Cloud op 21-12-2010 15:59 . Reden: ik is edit slet :+ ]

[DennusB]

Cloud schreef op dinsdag 21 december 2010 @ 15:57:
[...]

Je WHERE-clause filtert primair de records uit de resultset, niet de JOIN-clause. Oftewel de kolom T1.partnr moet geindexeerd worden om meer performance te halen. Filteren kost namelijk veel tijd (is wel afhankelijk van datatype).

Om het zoeken van de bijbehorende T2 records snel te houden is de index op de kolommen uit de join natuurlijk ook belangrijk dus die is prima

Duidelijk
Probleem is nu dat er (blijkbaar) toch duplicates voorkomen bij de import. Dus ik kan geen UNIQUE index maken op de T1.partnr. Hmmmm... shit.

[CRiMiNaL]

Je hoeft ook niet perse een UNIQUE index te maken, in MySQL kan je ook gewoon een index maken (b-tree) Dat gaat je al veel snelheidswinst opleveren

[Woy]

Cloud schreef op dinsdag 21 december 2010 @ 15:57:
[...]
Om het zoeken van de bijbehorende T2 records snel te houden is de index op de kolommen uit de join natuurlijk ook belangrijk dus die is prima

Maar de index op T1.lev_artnr zal daar niet voor gebruikt worden, immers is die al beschikbaar door het filter op T1.partnr

[cariolive23]

Kun je al een resultaat van EXPLAIN tonen? Dan krijgen we een beter idee van het probleem dan wat nu het geval is, we moeten er nu nog naar raden.

[DennusB]

cariolive23 schreef op dinsdag 21 december 2010 @ 16:07:
Kun je al een resultaat van EXPLAIN tonen? Dan krijgen we een beter idee van het probleem dan wat nu het geval is, we moeten er nu nog naar raden.

Sure.


BTW : Ik heb nu de index weggehaald op T1.lev_artnr en een index gemaakt op T1.partnr.
Resultaat:

code:

SELECT stock, partnr, omschrijving FROM T1 JOIN T2 ON T1.lev_artnr = T2.sku WHERE T1.partnr = 'B100888' LIMIT 0,1;
/* 0 rows affected, 0 rows found. Duration for 1 query: 7,816 sec. */

Wtf?

[ Voor 6% gewijzigd door DennusB op 21-12-2010 16:12 ]

[CRiMiNaL]

Je snapt dus niets van SQL en je gaat op de bonnefooi indexen verplaatsen en vraagt je dan af waarom het een lange tijd duurt? Lees je überhaupt wel wat o.a. Cloud uitlegt?

Je kan gewoon 2 indexen op 1 tabel hebben, dus waarom niet een index op T1.lev_artnr EN T1.partnr
de index op lev_artnr is PRIMARY en UNIQUE en partnr niets speciaals

[DennusB]

CRiMiNaL schreef op dinsdag 21 december 2010 @ 16:19:
Je snapt dus niets van SQL en je gaat op de bonnefooi indexen verplaatsen en vraagt je dan af waarom het een lange tijd duurt? Lees je überhaupt wel wat o.a. Cloud uitlegt?

Je kan gewoon 2 indexen op 1 tabel hebben, dus waarom niet een index op T1.lev_artnr EN T1.partnr
de index op lev_artnr is PRIMARY en UNIQUE en partnr niets speciaals

Kan jij een beetje je gemak houden met je grote klep? Ik kom hier niet voor niks hulp vragen.
Als er dan zo iemand als jou met z'n grote muil komt vertellen dat je er niks van kan wil ik je toch vriendelijk vragen eens bij jezelf te kijken of jij wel iets weet van "Sociaal doen"
Back ontopic : 2 indexen nu in T1. Query duurt nu ongeveer nog 1,7 seconde. Het gaat al beter

[Juup]

DennusB schreef op dinsdag 21 december 2010 @ 16:23:
Kan jij een beetje je gemak houden met je grote klep? Ik kom hier niet voor niks hulp vragen.
Als er dan zo iemand als jou met z'n grote muil komt vertellen dat je er niks van kan wil ik je toch vriendelijk vragen eens bij jezelf te kijken of jij wel iets weet van "Sociaal doen".

DennusB, ik wou je helpen maar als je zo reageert krijg je m'n hulp niet.

[CRiMiNaL]

We zijn hier niet om "Sociaal" te doen: We zij hier met z'n allen om mogelijke oplossing te suggereren voor problemen en daarbij word wel verwacht dat iemand met bepaalde basis informatie komt.
Een query van 1.7 seconden zegt niets, post eens een EXPLAIN van die 1.7 seconde durende query.

[Cloud]

Dus deze query:

SQL:

SELECT stock, partnr, omschrijving 
FROM T1 
JOIN T2 ON T1.lev_artnr = T2.sku 
WHERE T1.partnr = 'B100888' LIMIT 0,1;

duurt nu (met indices op T1.lev_artnr, T1.partnr en T2.sku) 1,7 seconden?

Zou je daar nog eens de output van EXPLAIN statement willen geven? Gezien de aantallen rijen vind ik het persoonlijk nog steeds veel te veel namelijk.

[RobIII]

Zo, 't was weer gezellig.