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

[Igor Babaev]

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)