TransWikia.com

Something is totally wrong with my query. .. Slow. .. Why?

Database Administrators Asked by Ingus on December 24, 2021

i have query with multiple JOINS and SUBQUERYS and it seems i have messed something up here. ..

It did worked when there was low data (data is not even that much now too) but now it is not even finishing query (even in MySQL workbench)

I know how to write simple join query but when it has to load data i mess up 🙁

SELECT l.id               AS lid, 
       l.docNr            AS receive_docNr, 
       l.thisTransport    AS receive_transport, 
       l.productNr        AS receive_productNr, 
       l.activityDate     AS receive_date, 
       h.ladingNr         AS receive_ladingNr, 
       l.place_count      AS receive_place_count, 
       h.cargo_status     AS receive_cargo_status, 
       h.clientCode       AS clientCode, 
       e.id               AS eid, 
       e.docNr            AS docNr, 
       e.deliverydate     AS deliveryDate, 
       e.activitydate     AS activityDate, 
       e.assistant_amount AS assistant_amount, 
       e.amount           AS amount, 
       e.productnr        AS productNr, 
       e.productumo       AS productUmo, 
       e.status           AS status, 
       e.orgline, 
       ni.name1, 
       ni.name2, 
       h.agreements       AS agreements, 
       nc.Name, 
       a.uom              AS uom, 
       au.uom             AS auuom, 
       (select z.status from        item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as istatus, 
       (select z.place_count from   item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iplace_count, 
       (select z.activityDate from  item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iactivityDate, 
       (select z.place_count from   item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iassistant_amount 

FROM cargo_line_receive AS l 

    LEFT JOIN item_ledger_entry AS e 
    ON e.cargoline = l.id 
    OR e.orgline = l.id 
              
    LEFT JOIN cargo_header_receive AS h 
    ON l.docNr = h.docNr 
          
    LEFT JOIN agreements_lines AS a 
    ON h.agreements = a.contractnr 
    AND e.productnr = a.item 
    AND e.resource = a.service 
             
    LEFT JOIN additional_uom AS au 
    ON e.productnr = au.productnr 
    AND au.status = 1 
    AND au.convert_from = 1 
             
    LEFT JOIN n_customers AS nc 
    ON h.clientCode = nc.Code 
          
    LEFT JOIN n_items AS ni 
    ON l.productNr = ni.code 
              
WHERE  h.clientCode = '999999999999' 
AND h.agreements = 'LIG00067' 
       
GROUP  BY l.id 
ORDER  BY l.activityDate ASC, l.docNr

EXPLAIN QUERY:
enter image description here

I did not know what i can remove from query so i left all.
If i removed subquerys from select nothing changed (so for me it seems to be some join)

Can anyone tell me what indexses should have here for each table?

EDIT: I have added indexes but nothing changed 🙁

ALTER TABLE cargo_header_receive ADD INDEX aaa (docNr, agreements, clientCode);
ALTER TABLE item_ledger_entry ADD INDEX bbb (cargoline, orgline, productNr, resource);
ALTER TABLE cargo_line_receive ADD INDEX ccc (docNr, productNr, activityDate);
ALTER TABLE agreements_lines ADD INDEX ddd (contractnr, item, service);
ALTER TABLE additional_uom ADD INDEX eee (productnr, status, convert_from);
ALTER TABLE n_customers ADD INDEX fff (Code);
ALTER TABLE n_items ADD INDEX ggg (code);

One Answer

That query seems to fetch 4 columns from one particular row of one table:

select ((four columns))
    from        item_ledger_entry as z
        where  ( z.orgLine = l.id OR z.cargoLine = l.id )
          AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1

Correct? If so, I suggest:

SELECT ....
    FROM ( SELECT a,b,c,d
               FROM item_ledger_entry z
               WHERE (   z.orgLine = l.id
                      OR z.cargoLine = l.id )
                 AND e.docNr = l.docNr
               ORDER  BY z.id DESC LIMIT  1
         ) AS z2
    JOIN FROM cargo_line_receive AS l 
                     ON ( z2.orgLine = l.id
                      OR  z2.cargoLine = l.id )

    LEFT JOIN ...

That will help performance some.

The OR hurt performance; you use it often. Is there some way to avoid it? I hesitate to mention using UNION because I am not sure the transformation will work easily.

WHERE  h.clientCode = '999999999999' 
AND h.agreements = 'LIG00067' 

begs for h to have INDEX(clientCode, agreements). (The order of those columns does not matter.)

GROUP  BY l.id 

worries me. It sounds like the "explode-implode" scenario. Note that (at least logically), first all the JOINs are done. This produces (potentially) a much larger table that any of the original ones. Then the GROUP BY implodes down to the original size. The typical remedy is to get the l.id values you ultimately need, then do the JOINs.

The high "Rows" for e confirms that OR is a big part of the performance issue. It is doing a full table scan -- probably repeatedly -- because of

ON e.cargoline = l.id 
OR e.orgline = l.id 

But, before I address that, Please justify or get rid of LEFT. Keep in mind that JOIN matches up rows from the two tables, whereas LEFT JOIN also takes any missing rows from the 'right' table, supplying NULLs to indicate that the row is missing.

If JOIN is warranted, then this might 'save the day':

JOIN  ( ( SELECT  FROM item_ledger_entry 
            WHERE  e.cargoline = l.id )
        UNION DISTINCT
        ( SELECT  FROM item_ledger_entry 
            WHERE  e.orgline = l.id )
      ) AS e

instead of

LEFT JOIN item_ledger_entry AS e 
ON e.cargoline = l.id 
OR e.orgline = l.id 

Note, e will need two separate indexes: INDEX(cargoline), INDEX(orgline)

(I'm sorry, but your query is so convoluted that each of my suggestions may fail for some reason. You can add the indexes safely; at worst, they might not 'help'.)

Answered by Rick James on December 24, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP