summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result30
-rw-r--r--mysql-test/t/subselect.test8
-rw-r--r--sql/item_subselect.cc77
-rw-r--r--sql/item_subselect.h13
-rw-r--r--sql/sql_select.cc84
-rw-r--r--sql/sql_select.h5
6 files changed, 172 insertions, 45 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d92f0d4b50e..a5eb79005ca 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1245,7 +1245,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where
2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index
drop table t1, t2, t3;
-create table t1 (a int, b int, index a (a));
+create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
@@ -1259,7 +1259,15 @@ a
explain select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ref a a 5 func 10 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_in a a 5 func 100 Using index
+select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+a
+2
+4
+explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_in a a 5 func 100 Using index; Using where
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1267,8 +1275,22 @@ a
explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ref a a 5 func 10 Using where
-2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using where; Using index
+2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using index
+2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 100 Using where; Using index
+insert into t1 values (3,31);
+select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+a
+2
+3
+4
+select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
+a
+2
+4
+explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_in a a 5 func 100 Using index; Using where
drop table t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index d723dc45bda..5a00bf42451 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -824,7 +824,7 @@ explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
drop table t1, t2, t3;
-create table t1 (a int, b int, index a (a));
+create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
create table t3 (a int, b int, index a (a));
insert into t1 values (1,10), (2,20), (3,30), (4,40);
@@ -841,8 +841,14 @@ insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);
select * from t2 where t2.a in (select a from t1);
explain select * from t2 where t2.a in (select a from t1);
+select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+insert into t1 values (3,31);
+select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
+select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);
+explain select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
drop table t1, t2, t3;
#
# alloc_group_fields() working
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index e3f7d157ecd..894173f4575 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -972,22 +972,75 @@ int subselect_simplein_engine::exec()
((Item_in_subselect *) item)->value= (!cond || cond->val_int()?1:0);
}
}
+ DBUG_RETURN(end_exec(table) || (error != 0));
+}
+
+int subselect_simplein_engine::end_exec(TABLE *table)
+{
+ DBUG_ENTER("subselect_simplein_engine::end_exec");
+ int error=0, tmp;
+ if ((tmp= table->file->extra(HA_EXTRA_NO_CACHE)))
{
- int tmp= 0;
- if ((tmp= table->file->extra(HA_EXTRA_NO_CACHE)))
- {
- DBUG_PRINT("error", ("extra(HA_EXTRA_NO_CACHE) failed"));
- error= 1;
- }
- if ((tmp= table->file->index_end()))
+ DBUG_PRINT("error", ("extra(HA_EXTRA_NO_CACHE) failed"));
+ error= 1;
+ }
+ if ((tmp= table->file->index_end()))
+ {
+ DBUG_PRINT("error", ("index_end() failed"));
+ error= 1;
+ }
+ if (error == 1)
+ table->file->print_error(tmp, MYF(0));
+ DBUG_RETURN(error != 0);
+}
+
+int subselect_indexin_engine::exec()
+{
+ DBUG_ENTER("subselect_indexin_engine::exec");
+ int error;
+ TABLE *table= tab->table;
+ ((Item_in_subselect *) item)->value= 0;
+ if ((tab->ref.key_err= (*tab->ref.key_copy)->copy()))
+ {
+ table->status= STATUS_NOT_FOUND;
+ error= -1;
+ }
+ else
+ {
+ error= table->file->index_read(table->record[0],
+ tab->ref.key_buff,
+ tab->ref.key_length,HA_READ_KEY_EXACT);
+ if (error && error != HA_ERR_KEY_NOT_FOUND)
+ error= report_error(table, error);
+ else
{
- DBUG_PRINT("error", ("index_end() failed"));
- error= 1;
+ for(;;)
+ {
+ error= 0;
+ table->null_row= 0;
+ if (!table->status)
+ {
+ if (!cond || cond->val_int())
+ {
+ ((Item_in_subselect *) item)->value= 1;
+ goto finish;
+ }
+ }
+ else
+ goto finish;
+ error= table->file->index_next_same(table->record[0],
+ tab->ref.key_buff,
+ tab->ref.key_length);
+ if (error && error != HA_ERR_KEY_NOT_FOUND)
+ {
+ error= report_error(table, error);
+ goto finish;
+ }
+ }
}
- if (error == 1)
- table->file->print_error(tmp, MYF(0));
}
- DBUG_RETURN(error != 0)
+finish:
+ DBUG_RETURN(end_exec(table) || (error != 0));
}
uint subselect_single_select_engine::cols()
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index f9fd30141af..5749220629f 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -184,6 +184,7 @@ public:
friend class select_exists_subselect;
friend class subselect_simplein_engine;
+ friend class subselect_indexin_engine;
};
/* IN subselect */
@@ -314,6 +315,7 @@ public:
struct st_join_table;
class subselect_simplein_engine: public subselect_engine
{
+protected:
st_join_table *tab;
Item *cond;
public:
@@ -330,4 +332,15 @@ public:
bool dependent() { return 1; }
bool uncacheable() { return 1; }
void exclude();
+ static int end_exec(TABLE *table);
+};
+
+class subselect_indexin_engine: public subselect_simplein_engine
+{
+public:
+ subselect_indexin_engine(THD *thd, st_join_table *tab_arg,
+ Item_subselect *subs, Item *where)
+ :subselect_simplein_engine(thd, tab_arg, subs, where)
+ {}
+ int exec();
};
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b9ba7b89c09..dca5f2019fe 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -32,7 +32,7 @@
const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
"MAYBE_REF","ALL","range","index","fulltext",
- "ref_or_null","simple_in"
+ "ref_or_null","simple_in","index_in"
};
static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
@@ -441,6 +441,42 @@ err:
}
/*
+ test if it is known for optimisation IN subquery
+
+ SYNOPSYS
+ JOIN::test_in_subselect
+ where - pointer for variable in which conditions should be
+ stored if subquery is known
+
+ RETURN
+ 1 - known
+ 0 - unknown
+*/
+
+bool JOIN::test_in_subselect(Item **where)
+{
+ if (conds->type() == Item::FUNC_ITEM &&
+ ((class Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
+ ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
+ ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM)
+ {
+ join_tab->info= "Using index";
+ *where= 0;
+ return 1;
+ }
+ if (conds->type() == Item::COND_ITEM &&
+ ((class Item_func *)this->conds)->functype() ==
+ Item_func::COND_AND_FUNC)
+ {
+ *where= conds;
+ join_tab->info= "Using index; Using where";
+ return 1;
+ }
+ return 0;
+}
+
+
+/*
global select optimisation.
return 0 - success
1 - go out
@@ -729,37 +765,33 @@ JOIN::optimize()
*/
if (!group_list && !order && !having &&
unit->item && unit->item->substype() == Item_subselect::IN_SUBS &&
- tables == 1 && join_tab[0].type == JT_EQ_REF &&
- conds &&
+ tables == 1 && conds &&
!unit->first_select()->next_select())
{
Item *where= 0;
- bool ok= 0;
- if (conds->type() == Item::FUNC_ITEM &&
- ((class Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
- ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
- ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM)
- {
- ok= 1;
- join_tab->info= "Using index";
- }
- else if (conds->type() == Item::COND_ITEM &&
- ((class Item_func *)this->conds)->functype() ==
- Item_func::COND_AND_FUNC)
+ if (join_tab[0].type == JT_EQ_REF)
{
- ok= 1;
- where= conds;
- join_tab->info= "Using index; Using where";
+ if (test_in_subselect(&where))
+ {
+ join_tab[0].type= JT_SIMPLE_IN;
+ error= 0;
+ DBUG_RETURN(unit->item->
+ change_engine(new subselect_simplein_engine(thd, join_tab,
+ unit->item,
+ where)));
+ }
}
-
- if (ok)
+ else if (join_tab[0].type == JT_REF)
{
- join_tab[0].type= JT_SIMPLE_IN;
- error= 0;
- DBUG_RETURN(unit->item->
- change_engine(new subselect_simplein_engine(thd, join_tab,
- unit->item,
- where)));
+ if (test_in_subselect(&where))
+ {
+ join_tab[0].type= JT_INDEX_IN;
+ error= 0;
+ DBUG_RETURN(unit->item->
+ change_engine(new subselect_indexin_engine(thd, join_tab,
+ unit->item,
+ where)));
+ }
}
}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index abfde528746..e7e22b0107d 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -76,7 +76,7 @@ typedef struct st_join_cache {
enum join_type { JT_UNKNOWN,JT_SYSTEM,JT_CONST,JT_EQ_REF,JT_REF,JT_MAYBE_REF,
JT_ALL, JT_RANGE, JT_NEXT, JT_FT, JT_REF_OR_NULL,
- JT_SIMPLE_IN};
+ JT_SIMPLE_IN, JT_INDEX_IN};
class JOIN;
@@ -277,7 +277,8 @@ class JOIN :public Sql_alloc
bool rollup_init();
bool rollup_make_fields(List<Item> &all_fields, List<Item> &fields,
Item_sum ***func);
- int JOIN::rollup_send_data(uint idx);
+ int rollup_send_data(uint idx);
+ bool test_in_subselect(Item **where);
};
View Lorry Controller Status