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

Optimizer chooses wrong strategy on delete

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.4.12
    • 11.1.0
    • Optimizer
    • Ubuntu 18.04, n.a.

    Description

      5 different queries which should do the same thing but 3 different execution plans:

      1st case customer complained (cdrs_process is much smaller than cdrs):

      EXPLAIN
      DELETE FROM cdrs
       WHERE uniqueid IN (SELECT uniqueid FROM cdrs_process)
      ;
      +------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+
      | id   | select_type        | table        | type            | possible_keys | key     | key_len | ref  | rows   | Extra       |
      +------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+
      |    1 | PRIMARY            | cdrs         | ALL             | NULL          | NULL    | NULL    | NULL | 197418 | Using where |
      |    2 | DEPENDENT SUBQUERY | cdrs_process | unique_subquery | PRIMARY       | PRIMARY | 122     | func | 1      | Using index |
      +------+--------------------+--------------+-----------------+---------------+---------+---------+------+--------+-------------+
      

      Can/should the optimizer not do this automatically with the new features?

      Rewriting to multi-table delete helped. We have seen some strange behaviour with aliasing but could not reproduce it. The next CEP seems ok:

      EXPLAIN
      DELETE c
        FROM cdrs AS c JOIN cdrs_process AS cp
       WHERE c.uniqueid = cp.uniqueid
      ;
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
      |    1 | SIMPLE      | cp    | index  | PRIMARY       | start   | 10      | NULL             | 300  | Using index |
      |    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |             |
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
      

      EXPLAIN
      DELETE cdrs
        FROM cdrs JOIN cdrs_process ON cdrs.uniqueid = cdrs_process.uniqueid
      ;
      +------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
      | id   | select_type | table        | type   | possible_keys | key     | key_len | ref                        | rows | Extra       |
      +------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
      |    1 | SIMPLE      | cdrs_process | index  | PRIMARY       | start   | 10      | NULL                       | 300  | Using index |
      |    1 | SIMPLE      | cdrs         | eq_ref | PRIMARY       | PRIMARY | 122     | test.cdrs_process.uniqueid | 1    |             |
      +------+-------------+--------------+--------+---------------+---------+---------+----------------------------+------+-------------+
      

      EXPLAIN
      DELETE c
        FROM cdrs AS c JOIN cdrs_process AS cp ON c.uniqueid = cp.uniqueid
      ;
       
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
      |    1 | SIMPLE      | cp    | index  | PRIMARY       | start   | 10      | NULL             | 300  | Using index |
      |    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |             |
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
      

      But this one is a bit less optimal compared to the previous 3:

      EXPLAIN
      DELETE c
        FROM cdrs AS c JOIN cdrs_process AS cp USING(uniqueid)
      ;
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+
      | id   | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra |
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+
      |    1 | SIMPLE      | cp    | ALL    | PRIMARY       | NULL    | NULL    | NULL             | 300  |       |
      |    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 122     | test.cp.uniqueid | 1    |       |
      +------+-------------+-------+--------+---------------+---------+---------+------------------+------+-------+
      

      Should be reproducible with the attached test data.

      Attachments

        Issue Links

          Activity

            People

              lstartseva Lena Startseva
              oli Oli Sennhauser
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.