From 1fe423aebaaefdae815662ce555703eb66b3f9e0 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 30 Sep 2005 15:21:37 +0400 Subject: BUG#12915: Added single-table UPDATE/DELTE ... ORDER BY ... LIMIT optimization: now can use index to find records to update/delete when there is no WHERE clause. mysql-test/r/update.result: Testcase for BUG#12915 mysql-test/t/update.test: Testcase for BUG#12915 sql/mysql_priv.h: BUG#12915: Added init_read_record_idx function. sql/opt_range.cc: BUG#12915: Added get_index_for_order() - find an index that can be used to get first N table records in given ordering cheaper then one would with full table scan. sql/opt_range.h: BUG#12915: Added get_index_for_order() function sql/records.cc: BUG#12915: Added init_read_record_idx(), rr_index() that allow to scan index using init_read_record()/read_record.read_record() sql/sql_delete.cc: BUG#12915: Added single-table DELETE ... ORDER BY ... LIMIT optimization: now can use index to find records to delete when there is no WHERE clause. sql/sql_update.cc: BUG#12915: Added single-table UPDATE ... ORDER BY ... LIMIT optimization: now can use index to find records to update when there is no WHERE clause. sql/structs.h: BUG#12915: Added init_read_record_idx(), rr_index() that allow to scan index using init_read_record()/READ_RECORD::read_record() --- mysql-test/r/update.result | 59 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 59 insertions(+) (limited to 'mysql-test/r/update.result') diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index d6c1118f90c..cf07487febc 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -263,3 +263,62 @@ test delete from t1 where count(*)=1; ERROR HY000: Invalid use of group function drop table t1; +create table t1 ( a int, index (a) ); +insert into t1 values (0),(0),(0),(0),(0),(0),(0),(0); +flush status; +select a from t1 order by a limit 1; +a +0 +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 1 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +flush status; +update t1 set a=unix_timestamp() order by a limit 1; +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 1 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 1 +Handler_read_rnd_next 0 +flush status; +delete from t1 order by a limit 1; +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 1 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +flush status; +delete from t1 order by a desc limit 1; +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 1 +Handler_read_rnd_next 9 +alter table t1 disable keys; +flush status; +delete from t1 order by a limit 1; +show status like 'handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 1 +Handler_read_rnd_next 9 +select count(*) from t1; +count(*) +5 +drop table t1; -- cgit v1.2.1