diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2016-07-11 22:01:24 +0300 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2016-07-12 21:49:16 +0300 |
commit | 0bb5d955423c2a7b29eab02e7bf6194ae003ae75 (patch) | |
tree | 13fc7100c9898c9dcfdefb86d3ad73467065bc65 | |
parent | 53e7fcca4191c47ca48c378163bcb93ef6e632a5 (diff) | |
download | mariadb-git-0bb5d955423c2a7b29eab02e7bf6194ae003ae75.tar.gz |
MDEV-10325: Queries examines all rows of a tables when it should not
The problem was introduced by 1859caf60b725f81f2ac6091eb44cb848a4a439a:
MDEV-10175: range optimizer calls records_in_range() for full extended keys
Make the range optimizer not call records_in_range() when it would
not give any benefit.
that patch used an incorrect way to check for full extended key. Now fixing
the check.
-rw-r--r-- | mysql-test/r/innodb_ext_key.result | 24 | ||||
-rw-r--r-- | mysql-test/t/innodb_ext_key.test | 28 | ||||
-rw-r--r-- | sql/opt_range_mrr.cc | 6 |
3 files changed, 55 insertions, 3 deletions
diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index cae402a9f12..bd4bdb28fff 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -1040,5 +1040,29 @@ a 1 drop table t1, t2; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-10325: Queries examines all rows of a tables when it should not +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int not null, +col1 varchar(32), +filler varchar(100), +key idx1(col1(10)), +primary key (pk) +)engine=innodb; +insert into t1 +select +A.a + 10*B.a + 100*C.a, +concat('1234567890-', 1000+ A.a + 10*B.a + 100*C.a), +repeat('filler-data-', 4) +from +t0 A, t0 B, t0 C; +# The following must use type=ALL (and NOT type=ref, rows=1) +explain select * from t1 where col1='1234567890-a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL idx1 NULL NULL NULL # Using where +drop table t0,t1; set optimizer_switch=@save_ext_key_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index 9f3a89ff948..ec774b638e8 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -693,5 +693,33 @@ drop table t1, t2; set optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-10325: Queries examines all rows of a tables when it should not +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int not null, + col1 varchar(32), + filler varchar(100), + key idx1(col1(10)), + primary key (pk) +)engine=innodb; + +insert into t1 +select + A.a + 10*B.a + 100*C.a, + concat('1234567890-', 1000+ A.a + 10*B.a + 100*C.a), + repeat('filler-data-', 4) +from + t0 A, t0 B, t0 C; + +--echo # The following must use type=ALL (and NOT type=ref, rows=1) +--replace_column 9 # +explain select * from t1 where col1='1234567890-a'; + +drop table t0,t1; + set optimizer_switch=@save_ext_key_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index 729c491a6f1..fbccb7c4e1d 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -278,14 +278,14 @@ walk_up_n_right: (1) - range analysis is used for estimating condition selectivity (2) - This is a unique key, and we have conditions for all its user-defined key parts. - (3) - The table uses extended keys, and we have conditions for - all key parts. + (3) - The table uses extended keys, this key covers all components, + and we have conditions for all key parts. */ if (!(cur->min_key_flag & ~NULL_RANGE) && !cur->max_key_flag && (!key_info || // (1) ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) key_info->flags & HA_NOSAME) || // (2) - (seq->param->table->s->use_ext_keys && // (3) + ((key_info->flags & HA_EXT_NOSAME) && // (3) (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) ) && range->start_key.length == range->end_key.length && |