How to speed up  SELECT JOIN query for large tables

How do i speed up SELECT JOIN query for 2 large tables? (teststock and prdbesarch)

I've been using this code for a while now and it seems to be working fine.

One table is teststock and looks like this:
------------------------------------------------
ID int(12) NO PRI auto_increment
CombArtNr varchar(9) NO
KlantNr int(5) NO 0
besnr int(6) NO 0
itemnr int(4) NO 0

prdbesarch looks like this:
------------------------------
Field Name Field Type AllowNull Key Default Extra
prdbesarch
ID int(25) NO PRI auto_increment
besdate date NO
klantnr int(5) NO
klantnaam varchar(64) NO geen naam
fland varchar(64) NO geen land
opmerkingen varchar(512) NO geen
faktuurnr int(6) NO 0
proformanr int(6) NO 0
leveringsnr int(6) NO 0
finstatus varchar(128) NO
besnr int(6) NO
refklant varchar(64) NO
itemnr int(4) NO
artcode varchar(6) NO
klrcode varchar(3) NO
omschr varchar(256) NO
qty decimal(6,2) NO
price decimal(8,2) NO
totaal decimal(8,2) NO 0.00
kg decimal(6,2) NO
intrastat int(8) NO


Today i've update the teststock to the point where it has around 300 000 lines.
prdbesarch has around 15 000 lines


The code basicly shows me the content of prdbesarch and then for each line it gives me the the total stock (fromstock), and available stock (reststock).

Is it maybe because i'm pushing the limits of MySQL? Or is it because my code is just to complex for 2 tables?

This is an example of the code i use.Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:

SELECT l.*, 
SUM(CASE g.KlantNr WHEN l.klantnr THEN CASE g.besnr WHEN l.besnr THEN CASE g.itemnr WHEN l.itemnr THEN 1 ELSE 0 END ELSE 0 END ELSE 0 END) "FromStock", 
SUM(CASE g.KlantNr WHEN '0' THEN 1 ELSE 0 END)  "RestStock", 
COUNT(CombArtNr) "AllStock" 
FROM ( 
    SELECT 
    prdbesarch.ID, 
    prdbesarch.besdate, 
    prdbesarch.klantnr, 
    prdbesarch.klantnaam, 
    prdbesarch.fland, 
    prdbesarch.opmerkingen, 
    prdbesarch.faktuurnr, 
    prdbesarch.proformanr, 
    prdbesarch.leveringsnr, 
    prdbesarch.finstatus, 
    prdbesarch.besnr, 
    prdbesarch.refklant, 
    prdbesarch.itemnr, 
    prdbesarch.artcode, 
    prdbesarch.klrcode, 
    prdbesarch.omschr, 
    prdbesarch.qty, 
    prdbesarch.price, 
    prdbesarch.totaal, 
    prdbesarch.kg, 
    prdbesarch.intrastat 
    FROM prdbesarch 
    WHERE (artcode LIKE 'A1101') 
    ) l 
    LEFT JOIN teststock g 
    ON CONCAT(l.artcode,'_',l.klrcode) = g.CombArtNr 
    GROUP BY g.CombArtNr, 
    l.ID, 
    l.klantnr, 
    l.besnr, 
    l.refklant, 
    l.itemnr, 
    l.artcode, 
    l.klrcode, 
    l.qty 
ORDER BY l.ID ASC