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
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);
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP