Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
None
-
None
Description
In version 10.5.17
The reordering algorithm looks very strange and the results are not the best.
explain extended SELECT o.Wache,oi.OrganisationID,sli.showItem |
FROM
|
rdShopOrder o JOIN |
rdShopOrderItems oi ON oi.ID=o.ID AND oi.status="unbearbeitet" JOIN |
rdWache w ON o.OrganisationID=w.OrganisationID AND w.ID=o.Wache JOIN |
rdShopItems i ON oi.ItemID=i.ID JOIN |
rdMPGAdresse a ON i.OrganisationID=a.OrganisationID AND FIND_IN_SET("Lieferant",a.rolle) AND FIND_IN_SET(a.ID, i.LieferantIDs) AND FIND_IN_SET("Shop",a.showTool) LEFT JOIN |
rdShopLieferantItems sli ON (sli.OrganisationID,sli.LieferantID,sli.ItemID,sli.showItem)=(i.OrganisationID,a.ID,i.ID,1) |
WHERE
|
|
|
(
|
a.autoCommit="immer" OR |
w.Shop_autoCommit="immer" OR |
(a.autoCommit="nur ausgewählte Dienststellen" AND w.Shop_autoCommit="nur ausgewählte Lieferanten") |
)
|
GROUP BY w.ID |
The query plan is as follows:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE | oi | ref | PRIMARY,status_OrganisationID | status_OrganisationID | 1 | const | 1671 | 100.00 | Using index condition; Using temporary; Using filesort |
1 | SIMPLE | i | eq_ref | PRIMARY,OrganisationID,Name | PRIMARY | 4 | qmsystems.oi.ItemID | 1 | 100.00 | |
1 | SIMPLE | o | eq_ref | PRIMARY,Wache,OrganisationID_Datum,OrganisationID_BuHa_Status | PRIMARY | 4 | qmsystems.oi.ID | 1 | 100.00 | |
1 | SIMPLE | w | eq_ref | PRIMARY,Ressource,Shop,Strassensperrung | PRIMARY | 2 | qmsystems.o.Wache | 1 | 100.00 | Using index condition; Using where |
1 | SIMPLE | a | ref | OrganisationID | OrganisationID | 2 | qmsystems.i.OrganisationID | 65 | 100.00 | Using index condition; Using where |
1 | SIMPLE | sli | eq_ref | PRIMARY,LieferantID_ItemID,OrganisationID_LieferantID_ItemID_showItem | PRIMARY | 6 | qmsystems.a.ID,qmsystems.oi.ItemID | 1 | 100.00 | Using where |
if you change the last LEFT JOIN as intended to a FULL JOIN, the result is:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE | sli | index | PRIMARY,LieferantID_ItemID,OrganisationID_LieferantID_ItemID_showItem | OrganisationID_LieferantID_ItemID_showItem | 10 | NULL | 0 | 0.00 | Using where; Using index; Using temporary; Using filesort |
1 | SIMPLE | w | ALL | PRIMARY,Ressource,Shop,Strassensperrung | NULL | NULL | NULL | 1294 | 100.00 | Using join buffer (flat, BNL join) |
1 | SIMPLE | oi | ref | PRIMARY,status_OrganisationID | status_OrganisationID | 1 | const | 1671 | 100.00 | Using index condition; Using where |
1 | SIMPLE | a | ref | PRIMARY,OrganisationID,DROP_INDEX_ID | DROP_INDEX_ID | 3 | qmsystems.sli.LieferantID | 1 | 100.00 | Using index condition; Using where |
1 | SIMPLE | i | eq_ref | PRIMARY,OrganisationID,Name | PRIMARY | 4 | qmsystems.sli.ItemID | 1 | 100.00 | Using where |
1 | SIMPLE | o | ref | PRIMARY,Wache,OrganisationID_Datum,OrganisationID_BuHa_Status | Wache | 3 | qmsystems.w.ID | 186 | 100.00 | Using index condition; Using where |
For me (looking at the tables), it is not obvious, how mariadb manages to move "sli" to the front and reorder the query - and has the result of "0 rows" - which should be filtered=100.00 not 0 - and as a result, this query should be faster than the left join - but the oposite is the case. I had to manually kill this query; the left join query runs in 0.084s.
I assume the probleme here is the result of 0 rows in the first place. The total result is 0 rows.