CODEWITHSUNDEEP
mysql
user3651819
user3651819

Reputation: 497

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.

Upvotes: 0

Views: 52

Answers (1)

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 1

Related Questions