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

Poor query plan chosen for ORDER BY query by a recent 10.1

    XMLWordPrintable

    Details

      Description

      Discovered as part of MDEV-9420. See these comments:

      https://mariadb.atlassian.net/browse/MDEV-9420?focusedCommentId=80164&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-80164

      https://mariadb.atlassian.net/browse/MDEV-9420?focusedCommentId=80165&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-80165

      Copying here:
      Checking problem #1.

      MariaDB 10.0:

      MariaDB [j10]> explain select * from point_activite 
      WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 \G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: point_activite
               type: index_merge
      possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass
                key: id_contact_campaign_pass,id_contact_Script
            key_len: 9,9
                ref: NULL
               rows: 1
              Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where

      MariaDB [j10]> explain select * from point_activite 
      WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 
      ORDER BY `IDPoint_Activite`\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: point_activite
               type: index_merge
      possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass
                key: id_contact_campaign_pass,id_contact_Script
            key_len: 9,9
                ref: NULL
               rows: 1
              Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where; Using filesort

      So, in 10.0, the plan is always to use index_merge to produce one row. Actually, two rows are produced and the query finishes in 0.05 sec or less.

      In 10.1.10:

      MariaDB [j10]> explain select * from point_activite 
      WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: point_activite
               type: index_merge
      possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass
                key: id_contact_campaign_pass,id_contact_Script
            key_len: 9,9
                ref: NULL
               rows: 1
              Extra: Using intersect(id_contact_campaign_pass,id_contact_Script); Using where

      The same so far. Adding ORDER BY:

      explain select * from point_activite 
      WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 
      ORDER BY `IDPoint_Activite`\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: point_activite
               type: index
      possible_keys: id_contact_Script,id_activity_list,id_contact_campaign_pass
                key: PRIMARY
            key_len: 8
                ref: NULL
               rows: 1997722
              Extra: Using where

      And the plan becomes much worse. It wants to read nearly 2M rows. Execution takes 1 min 39 sec.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration