Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30015

full join vs left join results in a table scan vs. 1 row access

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Not a Bug
    • None
    • N/A
    • N/A
    • 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.

      Attachments

        Activity

          People

            Unassigned Unassigned
            mokraemer Marc
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.