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

Optimizer does not use the index for SELECT while it gives notable improvement in the execution time when FORCEd

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      For the specific table and data:

      > show create table mll_app_magna_pure
      Create Table: CREATE TABLE `mll_app_magna_pure` (
      `mp_id` char(36) NOT NULL DEFAULT '',
      `mp_create_date` bigint(20) unsigned NOT NULL,
      `mp_create_user` varchar(40) NOT NULL DEFAULT '',
      `mp_change_date` bigint(20) unsigned NOT NULL,
      `mp_change_user` varchar(40) NOT NULL DEFAULT '',
      `mp_valid_from` bigint(20) unsigned NOT NULL,
      `mp_valid_to` bigint(20) unsigned NOT NULL,
      `mp_outputplate` varchar(30) DEFAULT NULL,
      `mp_transferplate` varchar(20) DEFAULT NULL,
      KEY `mp_id_idx` (`mp_id`),
      KEY `mp_transferplate_idx` (`mp_valid_to`,`mp_transferplate`,`mp_outputplate`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       
      > select count(*) from mll_app_magna_pure;
      1408028
       
      > select count(*) from mll_app_magna_pure where mp_valid_to=99991231235555;
      806751
       
      > SELECT count(*) FROM mll_app_magna_pure WHERE mp_valid_to=99991231235555 AND mp_transferplate='0502212000095962';
      0
      

      the index is not used for this query:

      > ANALYZE FORMAT=JSON
      SELECT SQL_NO_CACHE * FROM mll_app_magna_pure WHERE mp_valid_to=99991231235555 AND (mp_transferplate='0502212000095962' OR mp_outputplate='0502212000095962')\G
      ANALYZE
      {
      "query_block": {
      "select_id": 1,
      "r_loops": 1,
      "r_total_time_ms": 4160.197218,
      "table": {
      "table_name": "mll_app_magna_pure",
      "access_type": "ALL",
      "possible_keys": [
      "mp_sample_no_idx",
      "mp_valid_to",
      "mp_status_barcode_idx",
      "mp_exas_id_idx",
      "mp_cdna_charge_idx",
      "mp_transferplate_idx",
      "mp_mpr_no_idx"
      ],
      "r_loops": 1,
      "rows": 1364085,
      "r_rows": 1412113,
      "r_table_time_ms": 3975.964708,
      "r_other_time_ms": 184.1642187,
      "filtered": 49.99996185,
      "r_filtered": 0.00325753,
      "attached_condition": "mll_app_magna_pure.mp_valid_to = 99991231235555 and (mll_app_magna_pure.mp_transferplate = '0502212000095962' or mll_app_magna_pure.mp_outputplate = '0502212000095962')"
      }
      }
      }
      

      even though WHERE clause is covered by the index (in a way) and the index is clearly beneficial when forced:

      > ANALYZE FORMAT=JSON
      SELECT SQL_NO_CACHE * FROM mll_app_magna_pure FORCE INDEX (`mp_transferplate_idx`) WHERE mp_valid_to=99991231235555 AND (mp_transferplate='0502212000095962' OR mp_outputplate='0502212000095962');
      {
      "query_block": {
      "select_id": 1,
      "r_loops": 1,
      "r_total_time_ms": 162.173876,
      "table": {
      "table_name": "mll_app_magna_pure",
      "access_type": "ref",
      "possible_keys": ["mp_transferplate_idx"],
      "key": "mp_transferplate_idx",
      "key_length": "8",
      "used_key_parts": ["mp_valid_to"],
      "ref": ["const"],
      "r_loops": 1,
      "rows": 632958,
      "r_rows": 46,
      "r_table_time_ms": 161.9810586,
      "r_other_time_ms": 0.135228766,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "mll_app_magna_pure.mp_transferplate = '0502212000095962' or mll_app_magna_pure.mp_outputplate = '0502212000095962'"
      }
      }
      }
      

      and is used by the optimizer for other content of the table:

      MariaDB [test]> explain SELECT SQL_NO_CACHE * FROM mll_app_magna_pure WHERE mp_valid_to=99991231235555 AND (mp_transferplate='0502212000095962' OR mp_outputplate='0502212000095962');
      +------+-------------+--------------------+------+----------------------+----------------------+---------+-------+------+-----------------------+
      | id   | select_type | table              | type | possible_keys        | key                  | key_len | ref   | rows | Extra                 |
      +------+-------------+--------------------+------+----------------------+----------------------+---------+-------+------+-----------------------+
      |    1 | SIMPLE      | mll_app_magna_pure | ref  | mp_transferplate_idx | mp_transferplate_idx | 8       | const | 1    | Using index condition |
      +------+-------------+--------------------+------+----------------------+----------------------+---------+-------+------+-----------------------+
      1 row in set (0.037 sec)
      

      The statistics for the table in the real case (InnoDB persistent one) is close to reality and was collected over as many sample pages as we have data pages. But looks like number of rows that match the condition on the first column in the index forces optimizer to ignore it.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.