[MDEV-3255] LP:892725 - An efficient plan to execute a query is changed for a full scan plan after the first execution of PS Created: 2011-11-20  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug892725.xml    

 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)



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 892725

Generated at Thu Feb 08 06:47:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.