Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-09-14 12:12:54+02:00, mhansson@stripped +5 -0
Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
The optimizer takes different execution paths during EXPLAIN than SELECT,
this fix relates only to EXPLAIN, hence no behavior changes.
The test of sort keys for ORDER BY was prohibited from considering keys
that were mentioned in IGNORE KEYS FOR ORDER BY. This led to two
inconsistencies: One was that IGNORE INDEX FOR GROUP BY and
IGNORE INDEX FOR ORDER BY gave apparently different EXPLAINs; the latter
erroneously claimed to do filesort. The second inconsistency
is that the test of sort keys is called twice, finding a sort key the first
time but not the second time, leading to the mentioned filesort.
Fixed by making the test of sort keys consider all enabled
keys on the table. This test rejects keys that are not covering, and for
covering keys the hint should be ignored anyway.
mysql-test/r/group_by.result@stripped, 2007-09-14 12:12:52+02:00, mhansson@stripped +147 -1
Bug#30665: Changed test result. The plan gets more efficient here.
The output is included in order to show that it is still correct.
mysql-test/r/order_by.result@stripped, 2007-09-14 12:12:52+02:00, mhansson@stripped +294 -0
Bug#30665: Test result
mysql-test/t/group_by.test@stripped, 2007-09-14 12:12:52+02:00, mhansson@stripped +1 -0
Bug#30665: Changed test case to show correctness of changed plan
mysql-test/t/order_by.test@stripped, 2007-09-14 12:12:52+02:00, mhansson@stripped +53 -0
Bug#30665: Test case
sql/sql_select.cc@stripped, 2007-09-14 12:12:52+02:00, mhansson@stripped +3 -1
Bug#30665:
- the fix: Give test_if_skip_sort_order all keys not the subset of non-disabled keys.
- Added comment to test_if_skip_sort_order
diff -Nrup a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
--- a/mysql-test/r/group_by.result 2007-08-03 17:45:44 +02:00
+++ b/mysql-test/r/group_by.result 2007-09-14 12:12:52 +02:00
@@ -1096,7 +1096,153 @@ id select_type table type possible_keys
1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
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; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+25
+26
+27
+28
+29
+30
+31
+32
+33
+34
+35
+36
+37
+38
+39
+40
+41
+42
+43
+44
+45
+46
+47
+48
+49
+50
+51
+52
+53
+54
+55
+56
+57
+58
+59
+60
+61
+62
+63
+64
+65
+66
+67
+68
+69
+70
+71
+72
+73
+74
+75
+76
+77
+78
+79
+80
+81
+82
+83
+84
+85
+86
+87
+88
+89
+90
+91
+92
+93
+94
+95
+96
+97
+98
+99
+100
+101
+102
+103
+104
+105
+106
+107
+108
+109
+110
+111
+112
+113
+114
+115
+116
+117
+118
+119
+120
+121
+122
+123
+124
+125
+126
+127
+128
+129
+130
+131
+132
+133
+134
+135
+136
+137
+138
+139
+140
+141
+142
+143
+144
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
diff -Nrup a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
--- a/mysql-test/r/order_by.result 2007-08-05 02:15:24 +02:00
+++ b/mysql-test/r/order_by.result 2007-09-14 12:12:52 +02:00
@@ -1131,3 +1131,297 @@ id c3
186 14
196 14
DROP TABLE t1,t2;
+CREATE TABLE t1 (
+a INT,
+b INT,
+PRIMARY KEY (a),
+KEY ab(a, b)
+);
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
+INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
+INSERT INTO t1 SELECT a +16, b +16 FROM t1;
+INSERT INTO t1 SELECT a +32, b +32 FROM t1;
+INSERT INTO t1 SELECT a +64, b +64 FROM t1;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL ab 4 NULL 10 Using index for group-by
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+25
+26
+27
+28
+29
+30
+31
+32
+33
+34
+35
+36
+37
+38
+39
+40
+41
+42
+43
+44
+45
+46
+47
+48
+49
+50
+51
+52
+53
+54
+55
+56
+57
+58
+59
+60
+61
+62
+63
+64
+65
+66
+67
+68
+69
+70
+71
+72
+73
+74
+75
+76
+77
+78
+79
+80
+81
+82
+83
+84
+85
+86
+87
+88
+89
+90
+91
+92
+93
+94
+95
+96
+97
+98
+99
+100
+101
+102
+103
+104
+105
+106
+107
+108
+109
+110
+111
+112
+113
+114
+115
+116
+117
+118
+119
+120
+121
+122
+123
+124
+125
+126
+127
+128
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+SELECT @tmp_tables_after = @tmp_tables_before ;
+@tmp_tables_after = @tmp_tables_before
+1
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index
+SELECT variable_value INTO @tmp_tables_before
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+25
+26
+27
+28
+29
+30
+31
+32
+33
+34
+35
+36
+37
+38
+39
+40
+41
+42
+43
+44
+45
+46
+47
+48
+49
+50
+51
+52
+53
+54
+55
+56
+57
+58
+59
+60
+61
+62
+63
+64
+65
+66
+67
+68
+69
+70
+71
+72
+73
+74
+75
+76
+77
+78
+79
+80
+81
+82
+83
+84
+85
+86
+87
+88
+89
+90
+91
+92
+93
+94
+95
+96
+97
+98
+99
+100
+101
+102
+103
+104
+105
+106
+107
+108
+109
+110
+111
+112
+113
+114
+115
+116
+117
+118
+119
+120
+121
+122
+123
+124
+125
+126
+127
+128
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+SELECT @tmp_tables_after = @tmp_tables_before;
+@tmp_tables_after = @tmp_tables_before
+1
+DROP TABLE t1;
diff -Nrup a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
--- a/mysql-test/t/group_by.test 2007-07-31 08:12:19 +02:00
+++ b/mysql-test/t/group_by.test 2007-09-14 12:12:52 +02:00
@@ -811,6 +811,7 @@ EXPLAIN SELECT a FROM t1 IGNORE INDEX (P
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+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;
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
diff -Nrup a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
--- a/mysql-test/t/order_by.test 2007-08-05 02:15:24 +02:00
+++ b/mysql-test/t/order_by.test 2007-09-14 12:12:52 +02:00
@@ -779,3 +779,56 @@ EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BE
SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
DROP TABLE t1,t2;
+
+#
+# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
+#
+
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ PRIMARY KEY (a),
+ KEY ab(a, b)
+);
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
+INSERT INTO t1 SELECT a + 4, b + 4 FROM t1;
+INSERT INTO t1 SELECT a + 8, b + 8 FROM t1;
+INSERT INTO t1 SELECT a +16, b +16 FROM t1;
+INSERT INTO t1 SELECT a +32, b +32 FROM t1;
+INSERT INTO t1 SELECT a +64, b +64 FROM t1;
+
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+
+--disable_query_log
+SELECT variable_value INTO @tmp_tables_before
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+--enable_query_log
+
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a;
+
+# this query creates one temporary table in itself, which we are not
+# interested in.
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+
+SELECT @tmp_tables_after = @tmp_tables_before ;
+
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+
+SELECT variable_value INTO @tmp_tables_before
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a;
+
+# this query creates one temporary table in itself, which we are not
+# interested in.
+SELECT variable_value - 1 INTO @tmp_tables_after
+FROM information_schema.session_status
+WHERE variable_name='CREATED_TMP_TABLES';
+
+SELECT @tmp_tables_after = @tmp_tables_before;
+
+DROP TABLE t1;
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-08-26 16:49:45 +02:00
+++ b/sql/sql_select.cc 2007-09-14 12:12:52 +02:00
@@ -1681,7 +1681,7 @@ JOIN::exec()
test_if_skip_sort_order(&join_tab[const_tables], order,
select_limit, 0,
&join_tab[const_tables].table->
- keys_in_use_for_order_by))))
+ keys_in_use_for_query))))
order=0;
having= tmp_having;
select_describe(this, need_tmp,
@@ -12589,6 +12589,8 @@ find_field_in_item_list (Field *field, v
If we can use an index, the JOIN_TAB / tab->select struct
is changed to use the index.
+
+ The index must cover all fields in <order>, or it will not be considered.
Return:
0 We have to use filesort to do the sorting
| Thread |
|---|
| • bk commit into 5.1 tree (mhansson:1.2577) BUG#30665 | mhansson | 14 Sep |
