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

LP:892725 - An efficient plan to execute a query is changed for a full scan plan after the first execution of PS

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      In the following scenario a full scan plan to execute a single-table query is chosen for a non-first execution
      of the prepared statement created for this query:
      1. create a prepared statement for a query that requires a simple look-up with a primary key
      2. execute the prepared statement one or more times
      3. execute a query over the same table ignoring all indexes
      4. execute the prepared statement again
      You'll see that the last execution uses a full scan.

      The problem exists for mariadb-5.3 code line and can be demonstrated with the following test case:

      create table t1 (a int primary key, b int);
      insert into t1 values
      (7,70), (3,40), (4,40), (8,70), (1,70), (9,50), (2,70);

      prepare st from 'select * from t1 where a=8';

      flush status;
      execute st;
      show status like '%Handler_read%';
      flush status;
      execute st;
      show status like '%Handler_read%';
      flush status;
      select * from t1 use index() where a=3;
      show status like '%Handler_read%';
      flush status;
      execute st;
      show status like '%Handler_read%';

      deallocate prepare st;

      drop table t1;

      You can see the problem from the following output when running this test case:

      MariaDB [test]> prepare st from 'select * from t1 where a=8';
      Query OK, 0 rows affected (0.00 sec)
      Statement prepared

      MariaDB [test]>
      MariaDB [test]> flush status;
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> execute st;
      -------+

      a b

      -------+

      8 70

      -------+
      1 row in set (0.00 sec)

      MariaDB [test]> show status like '%Handler_read%';
      ----------------------------+

      Variable_name Value

      ----------------------------+

      Handler_read_first 0
      Handler_read_key 1
      Handler_read_next 0
      Handler_read_prev 0
      Handler_read_rnd 0
      Handler_read_rnd_next 0

      ----------------------------+
      6 rows in set (0.00 sec)

      MariaDB [test]> flush status;
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> execute st;
      -------+

      a b

      -------+

      8 70

      -------+
      1 row in set (0.00 sec)

      MariaDB [test]> show status like '%Handler_read%';
      ----------------------------+

      Variable_name Value

      ----------------------------+

      Handler_read_first 0
      Handler_read_key 1
      Handler_read_next 0
      Handler_read_prev 0
      Handler_read_rnd 0
      Handler_read_rnd_next 0

      ----------------------------+
      6 rows in set (0.00 sec)

      MariaDB [test]> flush status;
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> select * from t1 use index() where a=3;
      -------+

      a b

      -------+

      3 40

      -------+
      1 row in set (0.00 sec)

      MariaDB [test]> show status like '%Handler_read%';
      ----------------------------+

      Variable_name Value

      ----------------------------+

      Handler_read_first 0
      Handler_read_key 0
      Handler_read_next 0
      Handler_read_prev 0
      Handler_read_rnd 0
      Handler_read_rnd_next 8

      ----------------------------+
      6 rows in set (0.00 sec)

      MariaDB [test]> flush status;
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> execute st;
      -------+

      a b

      -------+

      8 70

      -------+
      1 row in set (0.00 sec)

      MariaDB [test]> show status like '%Handler_read%';
      ----------------------------+

      Variable_name Value

      ----------------------------+

      Handler_read_first 0
      Handler_read_key 0
      Handler_read_next 0
      Handler_read_prev 0
      Handler_read_rnd 0
      Handler_read_rnd_next 8

      ----------------------------+
      6 rows in set (0.01 sec)

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            ratzpo Rasmus Johansson added a comment -

            Launchpad bug id: 892725

            Show
            ratzpo Rasmus Johansson added a comment - Launchpad bug id: 892725

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                igor Igor Babaev
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: