I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins).
I need to get all 0 values from those tables in the last WHERE
clause.
I'm talking about the following query:
SELECT * FROM (
SELECT
p.product_id, p.productcode, p.product_naam, s.station_omschrijving,
(SELECT COALESCE(SUM(ab.bedrag),0) FROM afschrijvingen_bedragen ab
WHERE ab.product_id = p.product_id AND ab.station_id = pro.station_id) as afschrijvingenbedrag,
(SELECT COALESCE(SUM(dkb.bedrag),0) FROM directe_kosten_bedragen dkb
WHERE dkb.product_id = p.product_id AND dkb.station_id = pro.station_id) as directekostenbedrag,
(SELECT COALESCE(SUM(ikb.bedrag),0) FROM indirecte_kosten_bedragen ikb
WHERE ikb.product_id = p.product_id AND ikb.station_id = pro.station_id) as indirectekostenbedrag,
(SELECT COALESCE(SUM(mb.bedrag),0) FROM materiaalverbruik_bedragen mb
WHERE mb.product_id = p.product_id AND mb.station_id = pro.station_id) as materiaalverbruikbedrag,
(SELECT COALESCE(SUM(pb.bedrag),0) FROM personeelsinzet_bedragen pb
WHERE pb.product_id = p.product_id AND pb.station_id = pro.station_id) as personeelsinzetbedrag,
(SELECT SUM(pr.aantal) FROM productie pr
WHERE pr.product_id = p.product_id AND aantal != 0) as productieaantal
FROM producten p
INNER JOIN (SELECT DISTINCT product_id, station_id FROM productie) pro
ON pro.product_id = p.product_id
INNER JOIN (SELECT DISTINCT station_id, station_omschrijving FROM stations) s
ON s.station_id = pro.station_id
) innerQuery
WHERE innerQuery.afschrijvingenbedrag = 0
OR innerQuery.directekostenbedrag = 0
OR innerQuery.indirectekostenbedrag = 0
OR innerQuery.materiaalverbruikbedrag = 0
OR innerQuery.personeelsinzetbedrag = 0
An EXPLAIN SELECT
gives me these results:
How to speed up the query? re-do the index? query code?
SUM(bedrag)
in any of those cases actually yield 0 or would the 0 indicate that there were no matches (and, therefore, NULL was returned and subsequently replaced with a 0 by COALESCE)? I'm asking this because, if it's the latter case, then what you are essentially using the SUMs for is to check for existence of rows, and that may be better done differently.SUM(bedrag)
can actually yield 0. I added theCOALESCE
for setting 0 inNULL
fields.