[Commits] Rev 3490: MDEV-446: Reuse quick_condition_rows in fanout computations in file:///home/tsk/mprog/src/5.5-md446-use-quick-condition-rows/

timour at askmonty.org timour at askmonty.org
Fri Aug 17 16:06:30 EEST 2012


At file:///home/tsk/mprog/src/5.5-md446-use-quick-condition-rows/

------------------------------------------------------------
revno: 3490
revision-id: timour at askmonty.org-20120817130242-hd94ug10uvyikvo0
parent: sergii at pisem.net-20120811083110-abzobkos5o6aw5hi
committer: timour at askmonty.org
branch nick: 5.5-md446-use-quick-condition-rows
timestamp: Fri 2012-08-17 16:02:42 +0300
message:
  MDEV-446: Reuse quick_condition_rows in fanout computations
  
  Implementation:
  - Introduced POSITION::records_read_by_access_path to distinguish between
    the number of rows returned by the access method of a table, compared to
    the number of rows returned by the JOIN_TAB after applying the pushed where
    condition (saved in POSITION::records_read)
  - changed the type of JOIN_TAB::records_read to double.
  
  mysql-test/r/subselect_sj_mat.result:
    The change in %filtered rows is a result of changing the type of JOIN_TAB::records_read from ha_rows to double.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2012-05-18 01:01:13 +0000
+++ b/mysql-test/r/join_cache.result	2012-08-17 13:02:42 +0000
@@ -3044,7 +3044,7 @@ id	select_type	table	type	possible_keys
 1       SIMPLE  t3      ref     t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid       4       test.t1.metaid  1       Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1       SIMPLE  t4      eq_ref  PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4       test.t3.formatid        1       Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1       SIMPLE  t5      eq_ref  PRIMARY,t5_formattypeid PRIMARY 4       test.t4.formatclassid   1       Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
-1       SIMPLE  t9      index   PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8       NULL    2       Using where; Using index; Using join buffer (incremental, BNL join)
+1       SIMPLE  t9      ref     PRIMARY,t9_subgenreid,t9_metaid t9_metaid       4       test.t1.metaid  2       Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1       SIMPLE  t10     eq_ref  PRIMARY,t10_genreid     PRIMARY 4       test.t9.subgenreid      1       Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1       SIMPLE  t11     eq_ref  PRIMARY PRIMARY 4       test.t10.genreid        1       Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT t1.uniquekey, t1.xml AS affiliateXml,

=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2012-06-14 18:05:31 +0000
+++ b/mysql-test/r/select.result	2012-08-17 13:02:42 +0000
@@ -3460,8 +3460,8 @@ select 'In next EXPLAIN, B.rows must be
 explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where
-1       SIMPLE  B       ref     PRIMARY PRIMARY 8       const,test.A.e  10      
+1       SIMPLE  B       range   PRIMARY PRIMARY 8       NULL    26      Using index condition; Using where
+1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where; Using join buffer (flat, BNL join)
 drop table t1, t2;
 CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
 INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),

=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result	2012-05-21 18:54:41 +0000
+++ b/mysql-test/r/select_jcl6.result	2012-08-17 13:02:42 +0000
@@ -3471,8 +3471,8 @@ select 'In next EXPLAIN, B.rows must be
 explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where; Rowid-ordered scan
-1       SIMPLE  B       ref     PRIMARY PRIMARY 8       const,test.A.e  10      Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1       SIMPLE  B       range   PRIMARY PRIMARY 8       NULL    26      Using index condition; Using where; Rowid-ordered scan
+1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
 drop table t1, t2;
 CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
 INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),

=== modified file 'mysql-test/r/select_pkeycache.result'
--- a/mysql-test/r/select_pkeycache.result	2012-05-21 18:54:41 +0000
+++ b/mysql-test/r/select_pkeycache.result	2012-08-17 13:02:42 +0000
@@ -3460,8 +3460,8 @@ select 'In next EXPLAIN, B.rows must be
 explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5
 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where
-1       SIMPLE  B       ref     PRIMARY PRIMARY 8       const,test.A.e  10      
+1       SIMPLE  B       range   PRIMARY PRIMARY 8       NULL    26      Using index condition; Using where
+1       SIMPLE  A       range   PRIMARY PRIMARY 12      NULL    4       Using index condition; Using where; Using join buffer (flat, BNL join)
 drop table t1, t2;
 CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
 INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-07-25 16:41:48 +0000
+++ b/mysql-test/r/subselect.result	2012-08-17 13:02:42 +0000
@@ -558,7 +558,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1878,13 +1878,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))

=== modified file 'mysql-test/r/subselect_mat_cost.result'
--- a/mysql-test/r/subselect_mat_cost.result	2012-05-29 21:18:53 +0000
+++ b/mysql-test/r/subselect_mat_cost.result	2012-08-17 13:02:42 +0000
@@ -287,7 +287,7 @@ AND Code = Country;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY CountryLanguage range   PRIMARY,Language        Language        30      NULL    45      Using index condition; Using where; Rowid-ordered scan
 1       PRIMARY Country eq_ref  PRIMARY PRIMARY 3       world.CountryLanguage.Country   1       Using where
-2       MATERIALIZED    CountryLanguage ref     PRIMARY,Language        Language        30      const   47      Using index condition
+2       DEPENDENT SUBQUERY      CountryLanguage unique_subquery PRIMARY,Language        PRIMARY 33      func,const      1       Using index; Using where
 SELECT Country.Name
 FROM Country, CountryLanguage 
 WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-07-25 16:41:48 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-08-17 13:02:42 +0000
@@ -565,7 +565,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1885,13 +1885,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-07-25 16:41:48 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-08-17 13:02:42 +0000
@@ -561,7 +561,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1881,13 +1881,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-07-25 16:41:48 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-08-17 13:02:42 +0000
@@ -564,7 +564,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1884,13 +1884,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-07-25 16:41:48 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-08-17 13:02:42 +0000
@@ -561,7 +561,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1887,7 +1887,7 @@ Note	1003	select `test`.`t1`.`id` AS `id
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))

=== modified file 'mysql-test/r/subselect_sj_mat.result'
--- a/mysql-test/r/subselect_sj_mat.result	2012-06-19 12:06:45 +0000
+++ b/mysql-test/r/subselect_sj_mat.result	2012-08-17 13:02:42 +0000
@@ -94,7 +94,7 @@ a1	a2
 explain extended
 select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2i     index   it2i1,it2i3     it2i1   #       NULL    5       40.00   Using where; Using index; LooseScan
+1       PRIMARY t2i     index   it2i1,it2i3     it2i1   #       NULL    5       50.00   Using where; Using index; LooseScan
 1       PRIMARY t1i     ref     _it1_idx        _it1_idx        #       _ref_   1       100.00  
 Warnings:
 Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
@@ -117,7 +117,7 @@ a1	a2
 explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2i     index   it2i1,it2i2,it2i3       it2i3   #       NULL    5       40.00   Using where; Using index; LooseScan
+1       PRIMARY t2i     index   it2i1,it2i2,it2i3       it2i3   #       NULL    5       50.00   Using where; Using index; LooseScan
 1       PRIMARY t1i     ref     _it1_idx        _it1_idx        #       _ref_   1       100.00  
 Warnings:
 Note    1003    select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
@@ -319,7 +319,7 @@ where (a1, a2) in (select b1, b2 from t2
 (a1, a2) in (select c1, c2 from t3i
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t2i     index   it2i1,it2i2,it2i3       #       #       #       5       40.00   #
+1       PRIMARY t2i     index   it2i1,it2i2,it2i3       #       #       #       5       50.00   #
 1       PRIMARY t1i     ref     it1i1,it1i2,it1i3       #       #       #       1       100.00  #
 1       PRIMARY t3i     ref     it3i1,it3i2,it3i3       #       #       #       1       100.00  #
 1       PRIMARY t2i     ref     it2i1,it2i2,it2i3       #       #       #       2       100.00  #
@@ -407,7 +407,7 @@ id	select_type	table	type	possible_keys
 2       MATERIALIZED    t2      ALL     NULL    #       #       #       5       100.00  #
 4       MATERIALIZED    t3      ALL     NULL    #       #       #       4       100.00  #
 3       MATERIALIZED    t3      ALL     NULL    #       #       #       4       100.00  #
-7       UNION   t2i     index   it2i1,it2i2,it2i3       #       #       #       5       40.00   #
+7       UNION   t2i     index   it2i1,it2i2,it2i3       #       #       #       5       50.00   #
 7       UNION   t1i     ref     it1i1,it1i2,it1i3       #       #       #       1       100.00  #
 7       UNION   t3i     ref     it3i1,it3i2,it3i3       #       #       #       1       100.00  #
 7       UNION   t2i     ref     it2i1,it2i2,it2i3       #       #       #       2       100.00  #

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-07-25 16:41:48 +0000
+++ b/sql/item_subselect.cc	2012-08-17 13:02:42 +0000
@@ -4626,7 +4626,7 @@ double get_fanout_with_deps(JOIN *join,
         !tab->emb_sj_nest && 
         tab->records_read != 0)
     {
-      fanout *= rows2double(tab->records_read);
+      fanout *= tab->records_read;
     }
   } 
   return fanout;

=== modified file 'sql/opt_subselect.h'
--- a/sql/opt_subselect.h	2012-05-04 05:16:38 +0000
+++ b/sql/opt_subselect.h	2012-08-17 13:02:42 +0000
@@ -282,6 +282,7 @@ class Loose_scan_opt
     if (best_loose_scan_cost != DBL_MAX)
     {
       pos->records_read=    best_loose_scan_records;
+      pos->records_read_by_access_path= best_loose_scan_records;
       pos->key=             best_loose_scan_start_key;
       pos->loosescan_picker.loosescan_key=   best_loose_scan_key;
       pos->loosescan_picker.loosescan_parts= best_max_loose_keypart + 1;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-08-09 15:22:00 +0000
+++ b/sql/sql_select.cc	2012-08-17 13:02:42 +0000
@@ -5136,6 +5136,7 @@ void set_position(JOIN *join,uint idx,JO
   join->positions[idx].table= table;
   join->positions[idx].key=key;
   join->positions[idx].records_read=1.0;        /* This is a const table */
+  join->positions[idx].records_read_by_access_path= 1;
   join->positions[idx].ref_depend_map= 0;
 
 //  join->positions[idx].loosescan_key= MAX_KEY; /* Not a LooseScan */
@@ -5158,7 +5159,8 @@ void set_position(JOIN *join,uint idx,JO
 /* Estimate of the number matching candidates in the joined table */
 
 inline
-ha_rows matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint)
+ha_rows matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint,
+                                     bool *range_stats_used)
 {
   ha_rows records= s->found_records;
   /*
@@ -5178,7 +5180,10 @@ ha_rows matching_candidates_in_table(JOI
       heuristics at once.
     */
   if (s->table->quick_condition_rows != s->found_records)
+  {
     records= s->table->quick_condition_rows;
+    *range_stats_used= true;
+  }
 
   return records;
 }
@@ -5235,6 +5240,7 @@ best_access_path(JOIN      *join,
   bool best_uses_jbuf= FALSE;
   MY_BITMAP *eq_join_set= &s->table->eq_join_set;
   KEYUSE *hj_start_key= 0;
+  bool range_stats_used= false;
 
   disable_jbuf= disable_jbuf || idx == join->const_tables;  
 
@@ -5252,6 +5258,7 @@ best_access_path(JOIN      *join,
     TABLE *table= s->table;
     double best_records= DBL_MAX;
     uint max_key_part=0;
+    bool cur_range_stats_used;
 
     /* Test how we can use keys */
     rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE;  // Assumed records/key
@@ -5268,6 +5275,8 @@ best_access_path(JOIN      *join,
       key_part_map const_part= 0;
       /* The or-null keypart in ref-or-null access: */
       key_part_map ref_or_null_part= 0;
+      cur_range_stats_used= false;
+
       if (is_hash_join_key_no(key))
       {
         /* 
@@ -5397,7 +5406,10 @@ best_access_path(JOIN      *join,
                 empty interval we wouldn't have got here).
               */
               if (table->quick_keys.is_set(key))
+              {
                 records= (double) table->quick_rows[key];
+                cur_range_stats_used= true;
+              }
               else
               {
                 /* quick_range couldn't use key! */
@@ -5434,6 +5446,7 @@ best_access_path(JOIN      *join,
                   records > (double) table->quick_rows[key])
               {
                 records= (double) table->quick_rows[key];
+                cur_range_stats_used= true;
               }
             }
             /* Limit the number of matched rows */
@@ -5503,6 +5516,7 @@ best_access_path(JOIN      *join,
                 table->quick_n_ranges[key] == 1+test(ref_or_null_part)) //(C3)
             {
               tmp= records= (double) table->quick_rows[key];
+              cur_range_stats_used= true;
             }
             else
             {
@@ -5568,7 +5582,7 @@ best_access_path(JOIN      *join,
                     tmp= a;
                   set_if_bigger(tmp,1.0);
                 }
-                records = (ulong) tmp;
+                records= (ulong) tmp;
               }
 
               if (ref_or_null_part)
@@ -5598,6 +5612,7 @@ best_access_path(JOIN      *join,
                   records > (double) table->quick_rows[key])
               {
                 tmp= records= (double) table->quick_rows[key];
+                cur_range_stats_used= true;
               }
             }
 
@@ -5625,6 +5640,7 @@ best_access_path(JOIN      *join,
         best_key= start_key;
         best_max_key_part= max_key_part;
         best_ref_depends_map= found_ref;
+        range_stats_used= cur_range_stats_used;
       }
     } /* for each key */
     records= best_records;
@@ -5649,7 +5665,8 @@ best_access_path(JOIN      *join,
   {
     double join_sel= 0.1;
     /* Estimate the cost of  the hash join access to the table */
-    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint);
+    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint,
+                                                      &range_stats_used);
 
     tmp= s->quick ? s->quick->read_time : s->scan_time();
     tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
@@ -5708,7 +5725,8 @@ best_access_path(JOIN      *join,
       !(s->table->force_index && best_key && !s->quick) &&               // (4)
       !(best_key && s->table->pos_in_table_list->jtbm_subselect))        // (5)
   {                                             // Check full join
-    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint);
+    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint,
+                                                      &range_stats_used);
 
     /*
       Range optimizer never proposes a RANGE if it isn't better
@@ -5791,7 +5809,19 @@ best_access_path(JOIN      *join,
   }
 
   /* Update the cost information for the current partial plan */
-  pos->records_read= records;
+  if (!range_stats_used)
+  {
+    /*
+      Range access is chosen if there is a quick select, and there is no [eq]ref.
+    */
+    range_stats_used= test(s->quick && !best_key);
+  }
+  pos->where_part_selectivity= range_stats_used ?
+    1 : ((s->table && s->table->file->stats.records) ?
+         ((double)s->table->quick_condition_rows /
+          (double)s->table->file->stats.records) : 1);
+  pos->records_read_by_access_path= records;
+  pos->records_read= records * pos->where_part_selectivity;
   pos->read_time=    best;
   pos->key=          best_key;
   pos->table=        s;
@@ -7554,7 +7584,8 @@ get_best_combination(JOIN *join)
            sub-order
       */
       SJ_MATERIALIZATION_INFO *sjm= cur_pos->table->emb_sj_nest->sj_mat_info;
-      j->records= j->records_read= (ha_rows)(sjm->is_sj_scan? sjm->rows : 1);
+      j->records_read= sjm->is_sj_scan ? sjm->rows : 1;
+      j->records= j->records_read_by_access_path= (ha_rows) j->records_read;
       JOIN_TAB *jt;
       JOIN_TAB_RANGE *jt_range;
       if (!(jt= (JOIN_TAB*)join->thd->alloc(sizeof(JOIN_TAB)*sjm->tables)) ||
@@ -7616,7 +7647,8 @@ get_best_combination(JOIN *join)
       Save records_read in JOIN_TAB so that select_describe()/etc don't have
       to access join->best_positions[]. 
     */
-    j->records_read= (ha_rows)join->best_positions[tablenr].records_read;
+    j->records_read= join->best_positions[tablenr].records_read;
+    j->records_read_by_access_path= join->best_positions[tablenr].records_read_by_access_path;
     join->map2table[j->table->tablenr]= j;
 
     /* If we've reached the end of sjm nest, switch back to main sequence */
@@ -10355,7 +10387,7 @@ ha_rows JOIN_TAB::get_examined_rows()
     }
   }
   else
-    examined_rows= (ha_rows) records_read; 
+    examined_rows= records_read_by_access_path;
 
   return examined_rows;
 }
@@ -16523,6 +16555,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       tab->info="const row not found";
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
+      pos->records_read_by_access_path= 0;
       pos->ref_depend_map= 0;
       if (!table->pos_in_table_list->outer_join || error > 0)
         DBUG_RETURN(error);
@@ -16549,6 +16582,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       tab->info="unique row not found";
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
+      pos->records_read_by_access_path= 0;
       pos->ref_depend_map= 0;
       if (!table->pos_in_table_list->outer_join || error > 0)
         DBUG_RETURN(error);

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-06-06 19:26:40 +0000
+++ b/sql/sql_select.h	2012-08-17 13:02:42 +0000
@@ -282,8 +282,12 @@ typedef struct st_join_table {
   */
   double        read_time;
   
-  /* psergey-todo: make the below have type double, like POSITION::records_read? */
-  ha_rows       records_read;
+  /* Copy of POSITION::records_read, set by get_best_combination() */
+  double       records_read;
+  /*
+    Copy of POSITION::records_read_by_access_path, set by get_best_combination()
+  */
+  ha_rows      records_read_by_access_path;
   
   /* Startup cost for execution */
   double        startup_cost;
@@ -755,6 +759,22 @@ typedef struct st_position :public Sql_a
   */
   double records_read;
 
+  /*
+    The fanout of the access method before the pushed down conditions are
+    applied. If the chosen access method is equivalent to the pushed down
+    selection condition, this member is equal to records_read.
+  */
+  ha_rows records_read_by_access_path;
+
+
+  /*
+    The added selectivity of the part of the WHERE clause pushed to this JOIN
+    after applying the access method for this table. If an access method covers
+    the whole WHERE part, then this selectivity is 1 because the WHERE part
+    doesn't filter any additional rows in addition to the access method.
+  */
+  double where_part_selectivity;
+
   /* 
     Cost accessing the table in course of the entire complete join execution,
     i.e. cost of one access method use (e.g. 'range' or 'ref' scan ) times 



More information about the commits mailing list