1

I have a query which is taking up to 7 minutes to execute. My Question is, how am I able to optimize this query?

The EXPLAIN statement shows two bottlenecks which are following:

  1. table fmonatsstatistik has 1721051 Records
  2. table filialartikel has 268460 Records

Extra is saying Using sort_union, using where, using, temporary, using filesort which I assume is really bad.

Indexes filalartikel:

+---------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| filialartikel |          0 | PRIMARY        |            1 | FilialNr       | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| filialartikel |          0 | PRIMARY        |            2 | ArtikelNr      | A         |      256176 |     NULL | NULL   |      | BTREE      |         |               |
| filialartikel |          1 | ArtikelName    |            1 | ArtikelName    | A         |       15029 |     NULL | NULL   | YES  | BTREE      |         |               |
| filialartikel |          1 | ArtikelZusatz1 |            1 | ArtikelZusatz1 | A         |         392 |     NULL | NULL   | YES  | BTREE      |         |               |
| filialartikel |          1 | ArtikelZusatz2 |            1 | ArtikelZusatz2 | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| filialartikel |          1 | ArtikelZusatz3 |            1 | ArtikelZusatz3 | A         |          44 |     NULL | NULL   | YES  | BTREE      |         |               |
| filialartikel |          1 | Matchcode      |            1 | Matchcode      | A         |       12443 |     NULL | NULL   | YES  | BTREE      |         |               |
| filialartikel |          1 | RegalStandort  |            1 | RegalStandort  | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| filialartikel |          1 | EinkaugMenge   |            1 | EinkaufMenge   | A         |         232 |     NULL | NULL   | YES  | BTREE      |         |               |
| filialartikel |          1 | EinkaufSumme   |            1 | EinkaufSumme   | A         |        1012 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Indexes fmonatsstatistik:

+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fmonatsstatistik |          0 | PRIMARY    |            1 | Jahr        | A         |         432 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          0 | PRIMARY    |            2 | Monat       | A         |        3462 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          0 | PRIMARY    |            3 | ArtikelNr   | A         |      138994 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          0 | PRIMARY    |            4 | FilialNr    | A         |     1257526 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          1 | ArtikelNr  |            1 | ArtikelNr   | A         |       14366 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          1 | Woche      |            1 | Monat       | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          1 | Umsatz     |            1 | Umsatz      | A         |         851 |     NULL | NULL   | YES  | BTREE      |         |               |
| fmonatsstatistik |          1 | Betrag     |            1 | Betrag      | A         |        4757 |     NULL | NULL   | YES  | BTREE      |         |               |
| fmonatsstatistik |          1 | MWStBetrag |            1 | MWStBetrag  | A         |        7669 |     NULL | NULL   | YES  | BTREE      |         |               |
| fmonatsstatistik |          1 | GMEKPreis  |            1 | GMEKPreis   | A         |        2321 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          1 | Monat      |            1 | Monat       | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| fmonatsstatistik |          1 | GMVKPreis  |            1 | GMVKPreis   | A         |        2640 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Query:

    SELECT fmonatsstatistik.artikelnr,
       filialartikel.artikelname,
       artikel.artikelnummer,
       filialartikel.artikelzusatz1,
       filialartikel.artikelzusatz2,
       filialartikel.artikelzusatz3,
       filialen.filialname,
       (SELECT Concat_ws(' ', name1, name2)
        FROM   adressen
        WHERE  adressnr = (SELECT adressnr
                           FROM   artikellieferanten
                           WHERE  artikellieferanten.artikelnr =
                                  filialartikel.artikelnr
                                  AND artikellieferanten.filialnr =
                                      filialartikel.filialnr
                           ORDER  BY bevorzugt
                           LIMIT  1))                                         AS
       Lieferant,
       (SELECT Sum(filialartikel2.istbestandregal
                   + filialartikel2.istbestandlager)
        FROM   filialartikel AS filialartikel2
        WHERE  filialartikel.artikelnr = filialartikel2.artikelnr
               AND ( filialartikel2.filialnr = 0
                      OR '0' = '0' ))                                         AS
       Bestand,
       Sum(fmonatsstatistik.umsatz)                                           AS
       MengeSumme,
       Sum(fmonatsstatistik.betrag)                                           AS
       BetragSumme,
       Sum(fmonatsstatistik.betrag - fmonatsstatistik.mwstbetrag)             AS
       MWStExclSumme,
       (SELECT warengruppenname
        FROM   warengruppen
        WHERE  warengruppen.warengruppennr = artikel.warengruppennr)          AS
       WgName,
       (SELECT warenobergruppenname
        FROM   warenobergruppen
        WHERE  warenobergruppen.warenobergruppennr = (SELECT warenobergruppennr
                                                      FROM   warengruppen
                                                      WHERE
                       warengruppen.warengruppennr = artikel.warengruppennr)) AS
       WogName,
       filialartikel.regalstandort                                            AS
       RegalPlatz,
       Sum(fmonatsstatistik.betrag - fmonatsstatistik.mwstbetrag) -
Sum(fmonatsstatistik.umsatz) * ( fmonatsstatistik.gmekpreis + 0 ) * (
                                                                    (SELECT
waehrungkurs
                                                                     FROM
waehrungen
                                                                     WHERE
waehrungnr = (SELECT landw�hrung
              FROM   land
              WHERE  landnr = (SELECT landnr
                               FROM   filialen
                               WHERE
filialen.filial_id =
fmonatsstatistik.filialnr))) / (SELECT waehrungkurs
                                FROM   waehrungen
WHERE  waehrungnr = (SELECT waehrungnr
                     FROM   adressen
                     WHERE  adressnr = (SELECT adressnr
                                        FROM   artikellieferanten
                                        WHERE  artikellieferanten.artikelnr =
                                               filialartikel.artikelnr
                                               AND
                                               artikellieferanten.filialnr =
                                               filialartikel.filialnr
                                       LIMIT  1))) + 0 )               AS
       Rohertrag,
Sum(fmonatsstatistik.betrag - fmonatsstatistik.mwstbetrag) -
Sum(fmonatsstatistik.umsatz) * (
IF(filialartikel.einkaufmenge<>0, filialartikel.einkaufsumme/filialartikel.einkaufmenge, fmonatsstatistik.gmekpreis)
  + 0 ) * ( (SELECT waehrungkurs
             FROM   waehrungen
             WHERE  waehrungnr = (SELECT landw�hrung
                                  FROM   land
                                  WHERE  landnr = (SELECT landnr
                                                   FROM   filialen
                                                   WHERE
                        filialen.filial_id =
                        fmonatsstatistik.filialnr))) / (SELECT waehrungkurs
                        FROM   waehrungen
                        WHERE  waehrungnr = (SELECT waehrungnr
                                             FROM   adressen
                                             WHERE
                               adressnr = (SELECT adressnr
                                           FROM   artikellieferanten
                                           WHERE
                               artikellieferanten.artikelnr =
                               filialartikel.artikelnr
                               AND
            artikellieferanten.filialnr =
            filialartikel.filialnr
                                                     LIMIT  1))) + 0 ) AS
Rohertrag3
FROM   fmonatsstatistik
       INNER JOIN filialen
               ON fmonatsstatistik.filialnr = filialen.filial_id
       INNER JOIN filialartikel
               ON filialartikel.artikelnr = fmonatsstatistik.artikelnr
                  AND filialartikel.filialnr = fmonatsstatistik.filialnr
       INNER JOIN artikel
               ON artikel.artikelnr = fmonatsstatistik.artikelnr
WHERE  ( fmonatsstatistik.filialnr = 0
          OR '0' = '0' )
       AND ( artikel.warengruppennr = (SELECT fvalue
                                       FROM   query_params
                                       WHERE  fname = 'WarengruppenNr'
                                       LIMIT  1)
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'WarengruppenNr'
                  LIMIT  1) = 0 )
       AND ( artikel.sortimentnr = (SELECT fvalue
                                    FROM   query_params
                                    WHERE  fname = 'SortimentNr'
                                    LIMIT  1)
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'SortimentNr'
                  LIMIT  1) = 0 )
       AND ( CONVERT(artikel.artikelnummer, CHAR) =
             (SELECT fvalue
              FROM   query_params
              WHERE  fname = 'Artikelnummer'
              LIMIT  1)
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'Artikelnummer'
                  LIMIT  1) = '' )
       AND ( (SELECT warenobergruppennr
              FROM   warengruppen
              WHERE  warengruppen.warengruppennr = artikel.warengruppennr) =
                   (SELECT fvalue
                    FROM
                   query_params
                   WHERE
                   fname
                   =
                   'WarenObergruppenNr'
                   LIMIT  1)
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'WarenObergruppenNr'
                  LIMIT  1) = 0 )
       AND fmonatsstatistik.jahr * 12 + fmonatsstatistik.monat >= 24193
       AND fmonatsstatistik.jahr * 12 + fmonatsstatistik.monat <= 24204
       AND ( CONVERT(filialartikel.regalstandort, CHAR) =
             (SELECT fvalue
              FROM   query_params
              WHERE  fname = 'RegalStandort'
              LIMIT  1)
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'RegalStandort'
                  LIMIT  1) = '' )
       AND ( CONVERT(filialartikel.artikelname, CHAR) LIKE Concat('%',
                   (SELECT fvalue
                          FROM   query_params
                          WHERE  fname = 'ArtikelName'
                          LIMIT  1), '%')
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'ArtikelName'
                  LIMIT  1) = '' )
       AND ( CONVERT(filialartikel.artikelzusatz1, CHAR) LIKE
                   Concat('%', (SELECT fvalue
                          FROM   query_params
                          WHERE  fname = 'ArtikelZusatz1'
                          LIMIT  1), '%')
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'ArtikelZusatz1'
                  LIMIT  1) = '' )
       AND ( CONVERT(filialartikel.artikelzusatz2, CHAR) LIKE
                   Concat('%', (SELECT fvalue
                          FROM   query_params
                          WHERE  fname = 'ArtikelZusatz2'
                          LIMIT  1), '%')
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'ArtikelZusatz2'
                  LIMIT  1) = '' )
       AND ( CONVERT(filialartikel.artikelzusatz3, CHAR) LIKE
                   Concat('%', (SELECT fvalue
                          FROM   query_params
                          WHERE  fname = 'ArtikelZusatz3'
                          LIMIT  1), '%')
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'ArtikelZusatz3'
                  LIMIT  1) = '' )
       AND ( NOT Isnull((SELECT artikelnr
                         FROM   artikellieferanten
                         WHERE  adressnr = (SELECT fvalue
                                            FROM   query_params
                                            WHERE  fname = 'Lieferant'
                                            LIMIT  1)
                                AND
artikellieferanten.artikelnr = artikel.artikelnr
                                AND artikellieferanten.filialnr =
                                    fmonatsstatistik.filialnr
                         ORDER  BY bevorzugt
                         LIMIT  1))
              OR (SELECT fvalue
                  FROM   query_params
                  WHERE  fname = 'Lieferant'
                  LIMIT  1) = '' )
       AND ( umsatz != 0
              OR betrag != 0 )
GROUP  BY fmonatsstatistik.artikelnr,
          filialartikel.artikelname,
          Coalesce(filialartikel.artikelzusatz1, ''),
          Coalesce(filialartikel.artikelzusatz2, ''),
          Coalesce(filialartikel.artikelzusatz3, '')
ORDER  BY wogname ASC,
          wgname ASC,
          artikelname  

1 Answer 1

1

Don't even consider this way of building queries. Yes, it is clever to disable various ANDs by looking up a config value; but it is hideously slow.

Instead, fetch the config values, then build the query. In the process, leave out any AND that you can see will evaluate to TRUE:

AND ( ( ... ) OR ( SELECT fvalue ... ) = '' )

That is, fetch the query_params to decide the value of that SELECT, then either include this in the WHERE (if !='') or include it:

AND ( ... )

After you have fixed most of that, then dig into the SELECT clauses. This, for example, is costly; try to find a way that does not involve nested SELECTs:

              (  SELECT  waehrungkurs
                    FROM  waehrungen
                    WHERE  waehrungnr = 
                    (   SELECT  landw�hrung
                            FROM  land
                            WHERE  landnr = 
                            (   SELECT  landnr
                                    FROM  filialen
                                    WHERE  filialen.filial_id = fmonatsstatistik.filialnr))
              )

Start by turning that into a 3-table JOIN. But you should try to make it even simpler, since there are so many like that.

"filesort" is not "really bad". In fact you must have at least 2 of them -- one for GROUP BY and one for ORDER BY. (EXPLAIN fails to say how many are needed.)

Please provide the EXPLAIN; the number of Rows is often informative.

2
  • Thanks for you answert. I found a missing Index on table filialartikel. I've added a new one (ArtikelNr) as the one that was already existing was combined with filialnr. This speeded up the query from 7min to 0.5sec~
    – patrick_
    Commented Oct 17, 2017 at 8:16
  • I see duplicate indexes. I see indexes on flags -- usually will be ignored. I don't see any composite indexes (other than the PKs); they often help. So, further improvements may be possible.
    – Rick James
    Commented Oct 17, 2017 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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