[Commits] 117bcd5: MDEV-8646: Re-factor the code for working tables

Sergei Petrunia psergey at askmonty.org
Sun Dec 27 16:14:20 EET 2015


revision-id: 117bcd57f4032bfe54a09ee1e0219af29ec9cdcf
parent(s): 5adf3f06c18b5f20973f75bf6707ca33a2ca6c1d
committer: Sergei Petrunia
branch nick: 10.1-mdev8646
timestamp: 2015-12-28 01:14:19 +1100
message:

MDEV-8646: Re-factor the code for working tables

Previous code makes the EXPLAIN see the real query plans.
The first result is that EXPLAIN output now reflects the
realuty.
Adjust results for two queries in group_by.test that were
not showing "Using filesort" while they were actually doing it
(checked on current 10.1 and pre-MDEV-8646 tree).

---
 mysql-test/r/group_by.result |   35 +++++++++++++++++++++++++++++++++--
 mysql-test/t/group_by.test   |   25 +++++++++++++++++++++++++
 2 files changed, 58 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 9a259b3..daad4d1 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1336,12 +1336,43 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	144	
+#
+#  For this explain, the query plan is weird: if we are using 
+#  the primary key for reasons other than doing grouping, can't
+#  GROUP BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
+#  Here's a proof it is really doing sorting:
+flush status;
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+show status like 'Sort_%';
+Variable_name	Value
+Sort_merge_passes	0
+Sort_priority_queue_sorts	0
+Sort_range	0
+Sort_rows	144
+Sort_scan	1
+# Proof ends.
+# 
+#  For this explain, the query plan is weird: if we are using 
+#  the primary key for reasons other than doing sorting, can't
+#  ORDER BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
+#  Here's a proof it is really doing sorting:
+flush status;
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+show status like 'Sort_%';
+Variable_name	Value
+Sort_merge_passes	0
+Sort_priority_queue_sorts	0
+Sort_range	0
+Sort_rows	144
+Sort_scan	1
+# Proof ends.
+# 
 SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 a
 1
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 4b1cb82..a2c2789 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -992,8 +992,33 @@ EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
 EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
 EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
+
+--echo #
+--echo #  For this explain, the query plan is weird: if we are using 
+--echo #  the primary key for reasons other than doing grouping, can't
+--echo #  GROUP BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+--echo #  Here's a proof it is really doing sorting:
+flush status;
+--disable_result_log
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+--enable_result_log
+show status like 'Sort_%';
+--echo # Proof ends.
+--echo # 
+
+--echo #  For this explain, the query plan is weird: if we are using 
+--echo #  the primary key for reasons other than doing sorting, can't
+--echo #  ORDER BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+--echo #  Here's a proof it is really doing sorting:
+flush status;
+--disable_result_log
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+--enable_result_log
+show status like 'Sort_%';
+--echo # Proof ends.
+--echo # 
 SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
   IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;


More information about the commits mailing list