Hi Roy,
See inline for some comments on the changes in test results:
On 12/05/2011 17:26, Roy Lyseng wrote:
> #At file:///home/rl136806/mysql/repo/mysql-work0/ based on
revid:roy.lyseng@stripped
>
> 3371 Roy Lyseng 2011-05-12
> Bug#12316645: Wrong cost calculation with
optimizer_join_cache_level settings
>
> best_access_path() did not take properly into account the
setting of
> optimizer_join_cache_level when calculating the access path
that gives
> the smallest cost to access a given table.
>
> There are three changes applied in this bug fix:
> - best_access_path() properly accounts for join cache level 0, ie
> do not use join caching.
> - best_access_path() does account for join cache level
greater than 3
> when a table is outer-joined to the current join prefix.
> - best_access_path() does account for join cache level
greater than 3
> when a table is semi-joined to the current join prefix.
>
> Static properties for setting join buffering has been
centralized to
> a new function set_join_buffer_properties().
>
> Further on, setting of join cache level is changed.
> check_join_cache_usage() is renamed to setup_join_cache().
>
> Cost calculation for joins with join buffering is better than
it used
> to be, but it is still not fair. Sometimes, cost is calculated
using
> join buffering, but the decision has to be reverted later.
> Cost calculation for BKA join strategy is also not considered
properly
> (but this patch changes nothing in this area).
>
...
> === modified file 'mysql-test/r/join_cache_jcl4.result'
> --- a/mysql-test/r/join_cache_jcl4.result 2011-04-28 11:55:16 +0000
> +++ b/mysql-test/r/join_cache_jcl4.result 2011-05-12 15:25:46 +0000
> @@ -1449,8 +1449,8 @@ explain
> select t1.a, count(t2.p) as count
> from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
> id select_type table type possible_keys key key_len ref rows Extra
> -1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
> -1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where
> +1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using
temporary; Using filesort
> +1 SIMPLE t2 ALL i_a NULL NULL NULL 10 Using where; Using join buffer
(BNL, incremental buffers)
Hm. The text in the result file just above this diff is
The following must not show "using join cache":
Looking at the original bug report (Bug#40136) and fix, it seems the
intention is to prevent BKA in this case. So it is probably the
comment that is wrong. I also notice your patch changes the for this
query from BKA to BNL for higher values of join cache level. Join
caching appeared with the fix for Bug#42955, so maybe it is not longer
true that BKA cannot be used?
I also looked at handler request for the above query. The change does
not look like an improvement in this case. On the contrary, both
random and sequential accesses increases:
show status like 'Handler_read%';
Variable_name Value
Handler_read_first 1
-Handler_read_key 8
+Handler_read_key 10
Handler_read_last 0
-Handler_read_next 18
+Handler_read_next 8
Handler_read_prev 0
-Handler_read_rnd 0
-Handler_read_rnd_next 0
+Handler_read_rnd 8
+Handler_read_rnd_next 20
...
> === modified file 'mysql-test/r/join_nested.result'
> --- a/mysql-test/r/join_nested.result 2010-12-06 13:12:51 +0000
> +++ b/mysql-test/r/join_nested.result 2011-05-12 15:25:46 +0000
...
> @@ -1729,11 +1729,11 @@ LEFT JOIN
> (t5 JOIN t4 ON t5.carrier_id = t4.id)
> ON t4.carrier = t1.carrier;
> id select_type table type possible_keys key key_len ref rows Extra
> -1 SIMPLE t2 index package_id package_id 5 NULL 45 Using where; Using
index
> -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1
> +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 36
> +1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where;
Using index
> +1 SIMPLE t2 ref package_id package_id 5 test.t3.package_id 5 Using
where; Using index
> 1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1
> 1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index
> -1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where;
Using index
The handler statistics indicates that the old plan was probably better:
show status like 'Handler_read%';
Variable_name Value
-Handler_read_first 1
-Handler_read_key 56
+Handler_read_first 0
+Handler_read_key 74
Handler_read_last 0
-Handler_read_next 51
+Handler_read_next 38
Handler_read_prev 0
Handler_read_rnd 0
-Handler_read_rnd_next 0
+Handler_read_rnd_next 37
...
> === modified file 'mysql-test/r/select_all.result'
> --- a/mysql-test/r/select_all.result 2011-01-13 10:48:28 +0000
> +++ b/mysql-test/r/select_all.result 2011-05-12 15:25:46 +0000
> @@ -2398,7 +2398,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
> -1 SIMPLE t2 ref a a 23 test.t1.a 2 Using where
> +1 SIMPLE t2 ALL a NULL NULL NULL 5 Using where; Using join buffer
(BNL, incremental buffers)
The above query is probably no longer testing what it was intended
to test. ("Test case for bug 7520: a wrong cost of the index for a
BLOB field").
...
--
Øystein
