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

Support Index Condition Pushdown for clustered PK scans

    Details

      Description

      The following test will lock 3 records and their preceding gaps inside InnoDB, even though only the 2 first records match the condition id<=20:

      --source include/have_innodb.inc
      CREATE TABLE t1(id INT PRIMARY KEY, c INT NOT NULL) ENGINE=InnoDB;
      INSERT INTO t1 VALUES(10,10),(20,20),(30,30),(40,40);
      SELECT * FROM t1 WHERE id<=20 FOR UPDATE;
      DROP TABLE t1;
      

      InnoDB cannot be blamed for this, because the SQL layer is not invoking ha_innobase::idx_cond_push(), so prebuilt->idx_cond will be NULL and InnoDB cannot know that the id=30 record does not match the WHERE condition.

      To confirm this:

      break ha_innobase::create
      continue
      finish
      break lock_rec_lock
      break ha_innobase::delete_table
      continue
      

      There will be 3 calls to lock_rec_lock() before the DROP TABLE. There should be only 2 calls, with type=LOCK_ORDINARY to lock the keys id=10,id=20 and the gaps preceding them.

      Another reason to enable index condition pushdown (ICP) for table scan (or PRIMARY KEY scan) is that if the query would return BLOBs, it would be much more efficient to avoid copying and throwing away the BLOBs of non-matching rows.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                marko Marko Mäkelä
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: