Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
Description
EXPAIN shows the same plan, but ANALYSE STATEMENT shows reality: full table scan on second execution
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
|
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
|
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
|
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
|
|
|
create procedure p1()
|
EXPLAIN SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
|
|
|
create procedure p2()
|
ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
|
|
|
call p1();
|
call p1();
|
call p2();
|
call p2();
|
|
|
prepare stmt1 from "ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum";
|
|
|
execute stmt1;
|
execute stmt1;
|
|
|
drop procedure p1,p2;
|
drop table t1,t2;
|
for first execution:
"table": {
"table_name": "<derived2>",
"access_type": "ref",
"possible_keys": ["key0"],
"key": "key0",
"key_length": "7",
"used_key_parts": ["matintnum"],
"ref": ["test.m2.matintnum"],
"r_loops": 9,
"rows": 2,
"r_rows": 0.6667,
"r_total_time_ms": 0.0207,
"filtered": 100,
"r_filtered": 100,
"materialized": {
second execution:
"block-nl-join": {
"table":
,
"buffer_type": "flat",
"buffer_size": "256Kb",
"join_type": "BNL",
"attached_condition": "(d.matintnum = m2.matintnum)",
"r_filtered": 11.111,
"materialized": {
Attachments
Activity
This is a mariadb-5.5 bug (actually a mariadb-5.3 bug).
This bug could be observed in maridb-5.5:
MariaDB [test]> prepare stmt1 from "SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum";
|
Query OK, 0 rows affected (0.00 sec)
|
Statement prepared
|
|
|
MariaDB [test]> flush status;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> execute stmt1;
|
+--------+--------+
|
| pla_id | mat_id |
|
+--------+--------+
|
| 102 | 1 |
|
| 101 | 1 |
|
| 100 | 1 |
|
| 104 | 2 |
|
| 103 | 2 |
|
| 105 | 3 |
|
+--------+--------+
|
6 rows in set (0.00 sec)
|
|
|
MariaDB [test]> show status like '%Handler_read%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 21 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 6 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 6 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 27 |
|
+--------------------------+-------+
|
8 rows in set (0.00 sec)
|
|
|
MariaDB [test]> flush status;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> execute stmt1;
|
+--------+--------+
|
| pla_id | mat_id |
|
+--------+--------+
|
| 100 | 1 |
|
| 101 | 1 |
|
| 102 | 1 |
|
| 103 | 2 |
|
| 104 | 2 |
|
| 105 | 3 |
|
+--------+--------+
|
6 rows in set (0.00 sec)
|
|
|
MariaDB [test]> show status like '%Handler_read%';
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 12 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 0 |
|
| Handler_read_prev | 0 |
|
| Handler_read_rnd | 6 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 34 |
|
+--------------------------+-------+
|
8 rows in set (0.00 sec)
|
The different handler counters for the above two executions of the same prepared statement
show that the second execution uses an execution plan that differs from that used by the
first execution.
Will,
To whom do you address your comment?
This is a one line fix. Could be done any time for 10.1.
Yet I have no clue about the next 10.1 release.
On second execution temporary table for derived table created and filled way earlier (just after optimize derived first call). Then it has no keys to use:
m2 type: ALL q_keys: 0 refs: 0 key: -1 len: 0
d type: ref q_keys: 1 refs: 1 key: 0 len: 7
select used
refs: test.m2.matintnum
vs
Info about JOIN
m2 type: ALL q_keys: 0 refs: 0 key: -1 len: 0
d type: ALL q_keys: 0 refs: 0 key: -1 len: 0
select used
and so second time full scan used