asked on

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.

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

Open in new window

ASKER CERTIFIED SOLUTION

Rurne

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

24Carat

ASKER

Optimizing the query has improved the performance a lot.

I've alse created indexes on the teststock table and this helps a lot more even, however i'm unexperienced with indexes.

Wich is the best way to create indexes? Should i simple create 1 index named TSindex on all the field names? Or should i create indexes for each field?

How would you create the indexes on these tables?

Teststock is a table that changes frequently is that also an isue?

Rurne

The only benefit to a multicolumn index (especially in MyISAM tables) is to use it as a unique constraint. A multicolumn index only really hashes on the first column, so you are better off doing individual indexes on each of the columns in the GROUP BY statement.

Then again, it is a matter of perspective. As you've noted that teststock has frequen inserts/updates, too many indexes can slow performance as each has to be rehashed after the manipulation query. You may want to see how it performs with and without each of the indexes and to use some sample data to benchmark the insert and update queries:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark