diff options
| author | unknown <sergefp@mysql.com> | 2005-09-30 15:21:37 +0400 |
|---|---|---|
| committer | unknown <sergefp@mysql.com> | 2005-09-30 15:21:37 +0400 |
| commit | 1fe423aebaaefdae815662ce555703eb66b3f9e0 (patch) | |
| tree | 00b61167a0a17a8889f39b5ab9892775c48e78db /mysql-test/t/update.test | |
| parent | 7b8a6e458e6bcd3512b2ac2e88e8e02e7f2b4568 (diff) | |
| download | mariadb-git-1fe423aebaaefdae815662ce555703eb66b3f9e0.tar.gz | |
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()
Diffstat (limited to 'mysql-test/t/update.test')
| -rw-r--r-- | mysql-test/t/update.test | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 84e9ced2017..a37655b15fe 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -227,4 +227,33 @@ select DATABASE(); delete from t1 where count(*)=1; drop table t1; +# BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index +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; +show status like 'handler_read%'; + +flush status; +update t1 set a=unix_timestamp() order by a limit 1; +show status like 'handler_read%'; + +flush status; +delete from t1 order by a limit 1; +show status like 'handler_read%'; + +flush status; +delete from t1 order by a desc limit 1; +show status like 'handler_read%'; + +alter table t1 disable keys; + +flush status; +delete from t1 order by a limit 1; +show status like 'handler_read%'; + +select count(*) from t1; + +drop table t1; # End of 4.1 tests |