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

LP:1052523 - Unexpected index condition pushdown behaviour on range queries with InnoDB plugin

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: 5.5.29
    • Component/s: None
    • Labels:

      Description

      MariaDB version >= 5.5.23 does not use index condition pushdown for range query, when it should.

      I have tested it with both 5.5.23 and 5.5.27. The same query when run against MySQL 5.6.6-m9 and MariaDB 5.3.5 results in index condition pushdown being used.

      – Test case:

      CREATE TABLE `icp_test` (
        `i` int(11) NOT NULL,
        `k` int(11) NOT NULL,
        `v` char(32) DEFAULT NULL,
        KEY `k` (`k`,`i`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       
      SOURCE icp_test.dump
       
      EXPLAIN SELECT * FROM icp_test WHERE k BETWEEN 4000 AND 5000 AND i < 1000 ORDER BY k ASC LIMIT 10\G

      The data to use with the table is attached with this bug report.

      Explain output is seen on MariaDB 5.5.27:

      MariaDB [test]> explain select * from icp_test where k between 4000 and 5000 and i < 1000 order by k asc limit 10\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: icp_test
               type: range
      possible_keys: k
                key: k
            key_len: 8
                ref: NULL
               rows: 222436
              Extra: Using where
      1 row in set (0.00 sec)

      Explain output as seen on MySQL 5.6.6-m9:

      mysql> explain select sql_no_cache * from icp_test where k between 4000 and 5000 and i < 1000 order by k asc limit 10\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: icp_test
               type: range
      possible_keys: k
                key: k
            key_len: 8
                ref: NULL
               rows: 222436
              Extra: Using index condition
      1 row in set (0.00 sec)

      Explain output as seen on MariaDB 5.3.5:

      MariaDB [test]> explain select sql_no_cache * from icp_test where k between 4000 and 5000 and i < 1000 order by k asc limit 10\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: icp_test
               type: range
      possible_keys: k
                key: k
            key_len: 8
                ref: NULL
               rows: 223490
              Extra: Using index condition
      1 row in set (0.00 sec)

      This obviously means that the above query executes slowly on MariaDB 5.5.27.
      The query times on MariaDB 5.3.5 and MySQL 5.6.6-m9:

      +-----+------+----------------------------------+
      | i   | k    | v                                |
      +-----+------+----------------------------------+
      | 162 | 4010 | 0fcaae4367d0090810a291111b694709 |
      | 729 | 4016 | 8b52c5ceff7638e0522f7d3e23618736 |
      | 370 | 4032 | 7235547a596a1db3013d872cdbc7231a |
      | 707 | 4035 | 7f55c7c738ed3b56a91501c9fb59afe1 |
      | 645 | 4047 | e265b948627662ac3df9dcd373933335 |
      | 757 | 4051 | 9cdd8a926d21433e2af26536b078480c |
      | 572 | 4100 | f3daf307a6889403313ed700e27449b0 |
      |  89 | 4143 | 2cf9585a3ec980327790bd677fd97aa1 |
      | 321 | 4184 | 4fbc60b0aa1ebb58864ad0bdbacf6f14 |
      | 751 | 4185 | 0d47c6f6f50b78fb8b7d80d1c3e45937 |
      +-----+------+----------------------------------+
      10 rows in set (0.00 sec)

      The query time on MariaDB 5.5.27:

      +-----+------+----------------------------------+
      | i   | k    | v                                |
      +-----+------+----------------------------------+
      | 162 | 4010 | 0fcaae4367d0090810a291111b694709 |
      | 729 | 4016 | 8b52c5ceff7638e0522f7d3e23618736 |
      | 370 | 4032 | 7235547a596a1db3013d872cdbc7231a |
      | 707 | 4035 | 7f55c7c738ed3b56a91501c9fb59afe1 |
      | 645 | 4047 | e265b948627662ac3df9dcd373933335 |
      | 757 | 4051 | 9cdd8a926d21433e2af26536b078480c |
      | 572 | 4100 | f3daf307a6889403313ed700e27449b0 |
      |  89 | 4143 | 2cf9585a3ec980327790bd677fd97aa1 |
      | 321 | 4184 | 4fbc60b0aa1ebb58864ad0bdbacf6f14 |
      | 751 | 4185 | 0d47c6f6f50b78fb8b7d80d1c3e45937 |
      +-----+------+----------------------------------+
      10 rows in set (0.73 sec)

      This is because MariaDB 5.5.27 is reading more data (Handler_read_* = 19328), while MySQL 5.6.6-m9 and MariaDB 5.3.5 are reading far fewer data (Handler_read_* = 10)

        Attachments

          Activity

            People

            Assignee:
            elenst Elena Stepanova
            Reporter:
            ovaistariq Ovais Tariq
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration