user3651819 user3651819 - 1 month ago 9
MySQL Question

Query runs very slow

I have query which runs very slow. The goal of the query is to list all articles from table and quantity from table artikli. Then I want to see article sales which are listed in another tables (artikli_2016 for this year, and artikli_2015 for previous year)

SELECT a.ArtId, a.ArtSifra, a.ArtNaziv,a.ArtStanje,a.ArtStanjePalete, (a.ArtStanje + a.ArtStanjePalete) as total,
sum(g.Izlaz) as promet,c.JmOznaka,sum(f.Izlaz) as promet
FROM artikli a
LEFT JOIN artikli_2016 g ON a.ArtId= g.Artikal
LEFT JOIN artikli_2015 f ON a.ArtId= f.Artikal
left join jed_mjere c ON a.ArtMjera = c.jmid
WHERE a.ArtRoditelj != 0 AND a.ArtAktivan
GROUP BY a.ArtId ORDER BY a.ArtId ASC


Once I join artikli_2015 it gets very slow. It would be very helpful if someone can give me an advice or solution to run the query faster.

Answer

Due to 2 lots of multiple records (I suspect) then you probably need to use a couple of sub queries to get the sums. Assuming that jed_mjere only has at most 1 record for each ArtMjera.

SELECT a.ArtId, 
        a.ArtSifra, 
        a.ArtNaziv,
        a.ArtStanje,
        a.ArtStanjePalete, 
        (a.ArtStanje + a.ArtStanjePalete) as total, 
        g.promet,
        c.JmOznaka,
        f.promet
FROM artikli a 
LEFT OUTER JOIN 
(
    SELECT Artikal, SUM(Izlaz) AS promet
    FROM artikli_2016
    GROUP BY Artikal
)   g 
ON a.ArtId = g.Artikal 
LEFT OUTER JOIN 
(
    SELECT Artikal, SUM(Izlaz) AS promet
    FROM artikli_2015
    GROUP BY Artikal
)   f 
ON a.ArtId = f.Artikal 
LEFT OUTER JOIN jed_mjere c ON a.ArtMjera = c.jmid 
WHERE a.ArtRoditelj != 0 
AND a.ArtAktivan 
ORDER BY a.ArtId ASC

If your query is only dealing with a small number of records from artikli (ie a.ArtRoditelj != 0 AND a.ArtAktivan excludes most rows) then maybe worth doing a join to exclude records in the sub queries.

SELECT a.ArtId, 
        a.ArtSifra, 
        a.ArtNaziv,
        a.ArtStanje,
        a.ArtStanjePalete, 
        (a.ArtStanje + a.ArtStanjePalete) as total, 
        g.promet,
        c.JmOznaka,
        f.promet
FROM artikli a 
LEFT OUTER JOIN 
(
    SELECT g.Artikal, SUM(g.Izlaz) AS promet
    FROM artikli a 
    INNER JOIN artikli_2016 g
    ON a.ArtId = g.Artikal 
    WHERE a.ArtRoditelj != 0 
    AND a.ArtAktivan 
    GROUP BY g.Artikal
)   g 
ON a.ArtId = g.Artikal 
LEFT OUTER JOIN 
(
    SELECT f.Artikal, SUM(f.Izlaz) AS promet
    FROM artikli a 
    INNER JOIN artikli_2015 f
    ON a.ArtId = g.Artikal 
    WHERE a.ArtRoditelj != 0 
    AND a.ArtAktivan 
    GROUP BY f.Artikal
)   f 
ON a.ArtId = f.Artikal 
LEFT OUTER JOIN jed_mjere c ON a.ArtMjera = c.jmid 
WHERE a.ArtRoditelj != 0 
AND a.ArtAktivan 
ORDER BY a.ArtId ASC