MySQL Performance – Troubleshooting Slow InnoDB Queries

indexinnodbMySQLperformance

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?

Best Answer

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?

Related Question