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

Index_merge intersect plan is picked where ref access would be faster

    XMLWordPrintable

Details

    Description

      (I am using 10.4 for debugging but this most likely affects earlier versions too)

      Use the attached file to fill the dataset and then we get this table

      CREATE TABLE orders (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        customer_id int(10) unsigned DEFAULT NULL,
        is_processed tinyint(3) unsigned DEFAULT NULL,
       
        PRIMARY KEY (id),
        KEY customer_id (customer_id),
        KEY is_processed (is_processed)
      ) ENGINE=InnoDB;
      

      The query

      SELECT COUNT(id) 
      FROM 
        ordersis_processed = 1 AND 
        customer_id = 10000;
      

      Produces this query plan:

      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: orders
               type: index_merge
      possible_keys: customer_id,is_processed
                key: customer_id,is_processed
            key_len: 5,2
                ref: NULL
               rows: 9401
              Extra: Using intersect(customer_id,is_processed); Using where; Using index
      

      This is much slower than just doing a ref access on customer_id.

      Data distribution for is_processed is as follows

      +--------------+---------------------+
      | is_processed | count(is_processed) |
      +--------------+---------------------+
      |            0 |               10542 |
      |            1 |             1038034 |
      +--------------+---------------------+
      

      so the condition "is_processed=1" has 99% selectivity.
      Need to investigate, why does the optimizer choose to use it for index_merge?

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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