1

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:

enter image description here

How to speed up the query? re-do the index? query code?

  • Can 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. – Andriy M Mar 11 '16 at 13:44
  • Thanks for your comment @AndriyM SUM(bedrag) can actually yield 0. I added the COALESCE for setting 0 in NULL fields. – Daan Mar 14 '16 at 7:42
2
CREATE TEMPORARY TABLE pro ( PRIMARY KEY (product_id, station_id) ) ENGINE=InnoDB
    SELECT DISTINCT product_id, station_id FROM productie;
CREATE TEMPORARY TABLE s ( PRIMARY KEY (station_id)  ) ENGINE=InnoDB
    SELECT DISTINCT station_id, station_omschrijving FROM stations;

SELECT 
        ....
    FROM producten p
    JOIN pro  ON pro.product_id = p.product_id
    JOIN s    ON s.station_id = pro.station_id
HAVING p.afschrijvingenbedrag = 0
    OR p.... = 0
    ....;

Notes:

  • By creating tmp tables with useful indexes, those JOINs will run much faster.
  • I got rid of inner_query and changed WHERE inner_query. into HAVING p. This may not improve speed, but it simplifies the code.

Another thought... Did you really need the DISTINCTs? Keep in mind that SELECT DISTINCT a,b is like GROUP BY a,b, not like GROUP BY a.

Another thought...

s.station_omschrijving
...and...
INNER JOIN (SELECT DISTINCT station_id, station_omschrijving FROM stations) s
        ON s.station_id = pro.station_id

could perhaps be replaced by

( SELECT DISTINCT station_omschrijving FROM stations
      WHERE station_id = pro.station_id ) AS station_omschrijving
...and no join...

It is, however, unclear whether the JOIN is restricting the overall query and whether the DISTINCT is correct. I simply don't know what the data looks like in that table. Maybe DISTINCT station_omschrijving needs to be MAX(station_omschrijving) to avoid multiple rows?

  • Thanks for you answer, this has been a massive help, especially creating those temp tables. I do need the SELECT DISTINCT however. Is having a DISTINCT in a query a bottleneck ? – Daan Mar 14 '16 at 7:39
  • Would be replacing those temp tables, with views of those tables + indexes also help? Or is it even possible to add an index to a view ? – Daan Mar 14 '16 at 7:46
  • VIEWs are syntactic sugar, not performance boosters. INDEXes are the core to performance; however picking the right index can be a challenge. DISTINCT usually requires collecting the data, saving to an internal tmp table, sorting, and then de-duplicating. Sometimes it is not that complicated. – Rick James Mar 14 '16 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.