[Commits] Rev 4055: MDEV-4410: update does not want to use a covering index, but select uses it in file:///home/psergey/dev2/10.0/

Sergey Petrunya psergey at askmonty.org
Sun Mar 16 15:25:01 EET 2014


At file:///home/psergey/dev2/10.0/

------------------------------------------------------------
revno: 4055
revision-id: psergey at askmonty.org-20140316132457-uexfmaznnfz632pk
parent: psergey at askmonty.org-20140314175216-ztbe8wskbc5ou7vn
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0
timestamp: Sun 2014-03-16 14:24:57 +0100
message:
  MDEV-4410: update does not want to use a covering index, but select uses it
  - If an UPDATE 1) modifies the key it is using, and 2) has ORDER BY ... LIMIT
    which matches the key it is using,  Then we should use "Using buffer", not
    "Using filesort". 
=== modified file 'mysql-test/r/update.result'
--- a/mysql-test/r/update.result	2013-01-31 08:48:19 +0000
+++ b/mysql-test/r/update.result	2014-03-16 13:24:57 +0000
@@ -603,3 +603,30 @@ Variable_name	Value
 Handler_update	5
 ROLLBACK;
 DROP TABLE t1, t2;
+#
+# MDEV-4410: update does not want to use a covering index, but select uses it.
+#
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (key1 int, col1 int, key(key1));
+insert into t1
+select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C;
+# This must not have "Using filesort":
+explain 
+update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	key1	key1	5	NULL	100	Using where; Using buffer
+flush status;
+update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
+show status like 'Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	1
+Handler_read_prev	0
+Handler_read_rnd	2
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	0
+drop table t1, t2;
+# End of MariaDB 10.0 tests

=== modified file 'mysql-test/t/update.test'
--- a/mysql-test/t/update.test	2013-01-31 08:48:19 +0000
+++ b/mysql-test/t/update.test	2014-03-16 13:24:57 +0000
@@ -544,3 +544,25 @@ SHOW STATUS LIKE 'HANDLER_UPDATE';
 ROLLBACK;
 DROP TABLE t1, t2;
 
+--echo #
+--echo # MDEV-4410: update does not want to use a covering index, but select uses it.
+--echo #
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (key1 int, col1 int, key(key1));
+insert into t1
+select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C;
+
+--echo # This must not have "Using filesort":
+explain 
+update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
+
+flush status;
+update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
+# Handler_read_next should be 1 (due to LIMIT), not 100:
+show status like 'Handler_read%';
+
+drop table t1, t2;
+
+--echo # End of MariaDB 10.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2014-03-05 22:20:10 +0000
+++ b/sql/sql_select.cc	2014-03-16 13:24:57 +0000
@@ -24700,7 +24700,7 @@ test_if_cheaper_ordering(const JOIN_TAB
     - may set table->quick_condition_rows and table->quick_rows[...]
       to table->file->stats.records. 
 */
-
+//psergey: why didn't this change scanned_limit for MDEV-4410?
 uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select,
                          ha_rows limit, ha_rows *scanned_limit,
                          bool *need_sort, bool *reverse)

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2014-02-19 10:05:15 +0000
+++ b/sql/sql_update.cc	2014-03-16 13:24:57 +0000
@@ -502,7 +502,7 @@ int mysql_update(THD *thd,
   if (used_key_is_modified || order ||
       partition_key_modified(table, table->write_set))
   {
-    if (order && (need_sort || used_key_is_modified))
+    if (order && need_sort)
       query_plan.using_filesort= true;
     else
       query_plan.using_io_buffer= true;



More information about the commits mailing list