From: Date: April 23 2008 6:49am Subject: bk commit into 5.1 tree (igor:1.2562) BUG#35844 List-Archive: http://lists.mysql.com/commits/45859 X-Bug: 35844 Message-Id: <20080423044947.97FB063BCB5@igor-laptop.mysql.com> Below is the list of changes that have just been committed into a local 5.1 repository of igor. When igor 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, 2008-04-22 21:49:39-07:00, igor@stripped +4 -0 Fixed bug#35844. The function test_if_skip_sort_order ignored any covering index used for ref access of a table in a query with ORDER BY if this index was incompatible with the ORDER BY list and there was another covering index compatible with this list. As a result sub-optimal execution plans were chosen for some queries with ORDER BY clause. mysql-test/r/distinct.result@stripped, 2008-04-22 21:49:33-07:00, igor@stripped +1 -1 Adjusted results after the fix for bug#35844. mysql-test/r/order_by.result@stripped, 2008-04-22 21:49:33-07:00, igor@stripped +26 -0 Added a test case for bug#35844. mysql-test/t/order_by.test@stripped, 2008-04-22 21:49:33-07:00, igor@stripped +32 -0 Added a test case for bug#35844. sql/sql_select.cc@stripped, 2008-04-22 21:49:33-07:00, igor@stripped +6 -1 Fixed bug#35844. The function test_if_skip_sort_order ignored any covering index used for ref access of a table in a query with ORDER BY if this index was incompatible with the ORDER BY list and there was another covering index compatible with this list. As a result sub-optimal execution plans were chosen for some queries with ORDER BY clause. diff -Nrup a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result --- a/mysql-test/r/distinct.result 2008-03-26 09:37:34 -07:00 +++ b/mysql-test/r/distinct.result 2008-04-22 21:49:33 -07:00 @@ -694,7 +694,7 @@ INSERT INTO t1(a, b, c) VALUES (1, 1, 1) (1, 2, 3); EXPLAIN SELECT DISTINCT a, b, d, c FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 16 NULL 6 Using index +1 SIMPLE t1 range NULL PRIMARY 16 NULL 7 Using index for group-by; Using temporary SELECT DISTINCT a, b, d, c FROM t1; a b d c 1 1 0 1 diff -Nrup a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result --- a/mysql-test/r/order_by.result 2008-03-27 10:39:18 -07:00 +++ b/mysql-test/r/order_by.result 2008-04-22 21:49:33 -07:00 @@ -1458,3 +1458,29 @@ ORDER BY t2.c LIMIT 1; d 52.5 DROP TABLE t1,t2,t3; +CREATE TABLE t1 ( +id1 INT NULL, +id2 INT NOT NULL, +junk INT NOT NULL, +PRIMARY KEY (id1, id2, junk), +INDEX id2_j_id1 (id2, junk, id1) +); +INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4); +INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4); +INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4); +INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4); +INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4); +INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4); +INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4); +INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4); +INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4); +EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref id2_j_id1 id2_j_id1 4 const 4 Using where; Using index; Using filesort +SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1; +id1 +13 +14 +15 +16 +DROP TABLE t1; diff -Nrup a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test --- a/mysql-test/t/order_by.test 2008-03-27 10:39:19 -07:00 +++ b/mysql-test/t/order_by.test 2008-04-22 21:49:33 -07:00 @@ -1309,3 +1309,35 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1join_tab; ha_rows table_records= table->file->stats.records; bool group= join->group && order == join->group_list; + ha_rows ref_key_quick_rows= HA_POS_ERROR; LINT_INIT(best_key_parts); LINT_INIT(best_key_direction); LINT_INIT(best_records); @@ -12999,6 +13000,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR else keys= usable_keys; + if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) + ref_key_quick_rows= table->quick_rows[ref_key]; + read_time= join->best_positions[tablenr].read_time; for (uint i= tablenr+1; i < join->tables; i++) fanout*= join->best_positions[i].records_read; // fanout is always >= 1 @@ -13093,7 +13097,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR index_scan_time < read_time) { ha_rows quick_records= table_records; - if (is_best_covering && !is_covering) + if (is_best_covering && !is_covering || + is_covering && ref_key_quick_rows < select_limit) continue; if (table->quick_keys.is_set(nr)) quick_records= table->quick_rows[nr];