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

index_merge intersection is used where single ref access is probably faster

    Details

      Description

      Found this in MDEV-9420: A query uses index_merge access even if using single index would read just a few rows.

      Copying the latest two commentes from MDEV-9420:
      Trying with the original dataset, 10.1+MDEV-9457 patch:

       
      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

      Without ORDER BY, the plan is now the same:

      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

      (10.0 has the same plan).

      Let's look at the merged conditions:

      MariaDB [j10]> explain select * from point_activite WHERE `id_activity_list` = 1479 ;
      +------+-------------+----------------+------+------------------+------------------+---------+-------+------+-------+
      | id   | select_type | table          | type | possible_keys    | key              | key_len | ref   | rows | Extra |
      +------+-------------+----------------+------+------------------+------------------+---------+-------+------+-------+
      |    1 | SIMPLE      | point_activite | ref  | id_activity_list | id_activity_list | 9       | const |  290 |       |
      +------+-------------+----------------+------+------------------+------------------+---------+-------+------+-------+
       
      MariaDB [j10]> explain select * from point_activite WHERE `id_contact_Script` = 2075347 ;
      +------+-------------+----------------+------+-------------------+-------------------+---------+-------+------+-------+
      | id   | select_type | table          | type | possible_keys     | key               | key_len | ref   | rows | Extra |
      +------+-------------+----------------+------+-------------------+-------------------+---------+-------+------+-------+
      |    1 | SIMPLE      | point_activite | ref  | id_contact_Script | id_contact_Script | 9       | const |   16 |       |
      +------+-------------+----------------+------+-------------------+-------------------+---------+-------+------+-------+
       
      MariaDB [j10]> explain select * from point_activite WHERE `id_contact_campaign_pass` = 1920183 ;
      +------+-------------+----------------+------+--------------------------+--------------------------+---------+-------+------+-------+
      | id   | select_type | table          | type | possible_keys            | key                      | key_len | ref   | rows | Extra |
      +------+-------------+----------------+------+--------------------------+--------------------------+---------+-------+------+-------+
      |    1 | SIMPLE      | point_activite | ref  | id_contact_campaign_pass | id_contact_campaign_pass | 9       | const |    3 |       |
      +------+-------------+----------------+------+--------------------------+--------------------------+---------+-------+------+-------+

      So, index_merge computes intersection of a set of 3 rows and a set of 16 rows.

      This looks a bit weird - isn't it easier to read 3 rows from the table?

      OTOH, reading a few rows from the index = one disk read, reading each table record is also one disk read. Then, doing index_merge is doing two index reads plus reading one row, and it is possible that that's cheaper than doing an index read plus reading 3 rows from disk.

      The above reasoning ignores the costs of setting up index_merge, but so do our cost formulas. We could add a small penalty for initializing extra handler objects for index_merge.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: