3373 Marc Alff 2011-08-10 [merge]
Merge mysql-trunk --> mysql-trunk-wl5378
modified:
mysql-test/collections/default.experimental
mysql-test/include/mrr_tests.inc
mysql-test/r/innodb_mrr.result
mysql-test/r/innodb_mrr_cost.result
mysql-test/r/innodb_mrr_cost_icp.result
mysql-test/r/innodb_mrr_icp.result
mysql-test/r/innodb_mrr_none.result
mysql-test/r/myisam_mrr.result
mysql-test/r/myisam_mrr_cost.result
mysql-test/r/myisam_mrr_cost_icp.result
mysql-test/r/myisam_mrr_icp.result
mysql-test/r/myisam_mrr_none.result
mysql-test/suite/innodb/r/innodb-zip.result
mysql-test/suite/innodb/t/innodb-zip.test
mysql-test/suite/opt_trace/include/general2.inc
mysql-test/suite/opt_trace/r/general2_no_prot.result
mysql-test/suite/opt_trace/r/general2_ps_prot.result
sql/sql_select.cc
storage/innobase/handler/ha_innodb.cc
storage/perfschema/pfs_instr.cc
storage/perfschema/pfs_instr_class.cc
storage/perfschema/pfs_setup_actor.cc
storage/perfschema/pfs_setup_object.cc
support-files/mysql.spec.sh
3372 Marc Alff 2011-08-10
WL#5378 PERFORMANCE_SCHEMA SUMMARY BY USER / HOST
Improved test robustness for tests that are sensitive to connections.
Fixed an issue found by QA:
some threads would be removed from information_schema.processlist,
but not from performance_schema.threads.
The issue was that destroy_thread() could get stuck inside lf_hash_put_pins(),
because of leaks with PINS used by the LF_HASH,
and never actually free the instrumented thread.
The root cause is that lf_hash_search() can return NULL and still keep pined pins.
Fixed this by adding calls to lf_hash_search_unpin().
added:
mysql-test/suite/perfschema/include/no_protocol.inc
mysql-test/suite/perfschema/include/wait_for_pfs_thread_count.inc
modified:
mysql-test/suite/perfschema/include/connection_setup.inc
mysql-test/suite/perfschema/include/event_aggregate_setup.inc
mysql-test/suite/perfschema/include/stage_setup.inc
mysql-test/suite/perfschema/include/table_aggregate_setup.inc
mysys/my_thr_init.c
storage/perfschema/pfs_account.cc
storage/perfschema/pfs_host.cc
storage/perfschema/pfs_instr.cc
storage/perfschema/pfs_instr_class.cc
storage/perfschema/pfs_setup_actor.cc
storage/perfschema/pfs_setup_object.cc
storage/perfschema/pfs_user.cc
=== modified file 'mysql-test/collections/default.experimental'
--- a/mysql-test/collections/default.experimental 2011-08-04 07:30:12 +0000
+++ b/mysql-test/collections/default.experimental 2011-08-05 10:26:13 +0000
@@ -15,7 +15,6 @@ main.subquery_sj_none_jcl6 @solaris
main.subquery_sj_none_jcl7 @solaris # Bug#11753919 2010-01-20 alik Several test cases fail on Solaris with error Thread stack overrun
main.wait_timeout @solaris # Bug#11758972 2010-04-26 alik wait_timeout fails on OpenSolaris
main.kill @freebsd # Bug#12619719 2011-08-04 Occasional failure in PB2
-main.innodb_mrr_none # Bug#12619744 2011-08-04 Occasional failure in PB2
innodb.innodb_monitor # Bug#12320827 2011-08-04 Occasional failure in PB2
innodb.innodb_bug56143 @solaris # Bug#11765460 2011-08-04 Occasional failure in PB2
=== modified file 'mysql-test/include/mrr_tests.inc'
--- a/mysql-test/include/mrr_tests.inc 2011-05-05 11:10:39 +0000
+++ b/mysql-test/include/mrr_tests.inc 2011-08-05 08:37:06 +0000
@@ -209,10 +209,12 @@ set read_rnd_buffer_size=64;
# By default InnoDB will fill values only for key parts used by the query,
# which will cause DS-MRR to supply an invalid tuple on scan restoration.
-# Verify that DS-MRR's code extra(HA_EXTRA_RETRIEVE_ALL_COLS) call has effect:
+# This test was originally developed for verifying that DS-MRR's code
+# extra(HA_EXTRA_RETRIEVE_ALL_COLS) call has effect. This has now been
+# replaced by using the table's read_set bitmap.
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
@@ -221,9 +223,17 @@ insert into t2 select
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+# The use of "force index" is to ensure the query is done as a range scan.
+# Without "force index", InnoDB's record count estimate is sometimes
+# ~400 instead of 1000, which causes a table scan.
+let query=
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
+
+eval EXPLAIN $query;
+eval $query;
+
drop table t2;
# Try a very big rowid
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/innodb_mrr.result 2011-08-05 08:37:06 +0000
@@ -389,18 +389,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 99 Using where; Using MRR
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 99 Using where; Using MRR
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/innodb_mrr_cost.result'
--- a/mysql-test/r/innodb_mrr_cost.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/innodb_mrr_cost.result 2011-08-05 08:37:06 +0000
@@ -389,18 +389,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 99 Using where
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 99 Using where
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/innodb_mrr_cost_icp.result'
--- a/mysql-test/r/innodb_mrr_cost_icp.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/innodb_mrr_cost_icp.result 2011-08-05 08:37:06 +0000
@@ -389,18 +389,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 99 Using index condition
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 99 Using index condition
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/innodb_mrr_icp.result'
--- a/mysql-test/r/innodb_mrr_icp.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/innodb_mrr_icp.result 2011-08-05 08:37:06 +0000
@@ -389,18 +389,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 99 Using index condition; Using MRR
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 99 Using index condition; Using MRR
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/innodb_mrr_none.result'
--- a/mysql-test/r/innodb_mrr_none.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/innodb_mrr_none.result 2011-08-05 08:37:06 +0000
@@ -388,18 +388,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 99 Using where
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 99 Using where
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/myisam_mrr.result 2011-08-05 08:37:06 +0000
@@ -392,18 +392,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 92 Using where; Using MRR
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 92 Using where; Using MRR
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/myisam_mrr_cost.result'
--- a/mysql-test/r/myisam_mrr_cost.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/myisam_mrr_cost.result 2011-08-05 08:37:06 +0000
@@ -392,18 +392,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 92 Using where
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 92 Using where
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/myisam_mrr_cost_icp.result'
--- a/mysql-test/r/myisam_mrr_cost_icp.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/myisam_mrr_cost_icp.result 2011-08-05 08:37:06 +0000
@@ -392,18 +392,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 92 Using index condition
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 92 Using index condition
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/myisam_mrr_icp.result'
--- a/mysql-test/r/myisam_mrr_icp.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/myisam_mrr_icp.result 2011-08-05 08:37:06 +0000
@@ -392,18 +392,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 92 Using index condition; Using MRR
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 92 Using index condition; Using MRR
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/r/myisam_mrr_none.result'
--- a/mysql-test/r/myisam_mrr_none.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/myisam_mrr_none.result 2011-08-05 08:37:06 +0000
@@ -391,18 +391,21 @@ set @read_rnd_buffer_size_save= @@read_r
set read_rnd_buffer_size=64;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) );
+create table t2(a char(8), b char(8), c char(8), filler char(100), key k1(a,b,c) );
insert into t2 select
concat('a-', 1000 + A.a, '-a'),
concat('b-', 1000 + B.a, '-b'),
concat('c-', 1000 + C.a, '-c'),
'filler'
from t1 A, t1 B, t1 C;
-explain
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+EXPLAIN select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 92 Using where
-select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
+1 SIMPLE t2 range k1 k1 9 NULL 92 Using where
+select count(length(a) + length(filler))
+from t2 force index (k1)
+where a>='a-1000-a' and a <'a-1001-a';
count(length(a) + length(filler))
100
drop table t2;
=== modified file 'mysql-test/suite/innodb/r/innodb-zip.result'
--- a/mysql-test/suite/innodb/r/innodb-zip.result 2011-06-15 08:02:11 +0000
+++ b/mysql-test/suite/innodb/r/innodb-zip.result 2011-08-08 09:42:09 +0000
@@ -63,38 +63,38 @@ row_format=compressed;
create table t14(a int primary key) engine=innodb key_block_size=9;
Warnings:
Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=9.
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t0 Compact
-test t00 Compact
-test t1 Compact
-test t10 Dynamic
-test t11 Compressed
-test t12 Compressed
-test t13 Compressed
-test t14 Compact
-test t2 Redundant
-test t3 Compact
-test t4 Compact
-test t5 Redundant
-test t6 Redundant
-test t7 Redundant
-test t8 Compact
-test t9 Compact
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t0 Compact 16384 0
+test t00 Compact 16384 0
+test t1 Compact 16384 0
+test t10 Dynamic 16384 0
+test t11 Compressed 1024 0
+test t12 Compressed 1024 0
+test t13 Compressed 8192 0
+test t14 Compact 16384 0
+test t2 Redundant 16384 0
+test t3 Compact 16384 0
+test t4 Compact 16384 0
+test t5 Redundant 16384 0
+test t6 Redundant 16384 0
+test t7 Redundant 16384 0
+test t8 Compact 16384 0
+test t9 Compact 16384 0
drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
alter table t1 key_block_size=0;
alter table t1 row_format=dynamic;
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t1 Dynamic
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t1 Dynamic 16384 0
alter table t1 row_format=compact;
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t1 Compact
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t1 Compact 16384 0
alter table t1 row_format=redundant;
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t1 Redundant
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t1 Redundant 16384 0
drop table t1;
create table t1(a int not null, b text, index(b(10))) engine=innodb
key_block_size=1;
@@ -111,10 +111,10 @@ rollback;
select a,left(b,40) from t1 natural join t2;
a left(b,40)
1 1abcdefghijklmnopqrstuvwxyzAAAAAAAAAAAAA
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t1 Compressed
-test t2 Compact
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t1 Compressed 2048 1024
+test t2 Compact 16384 0
drop table t1,t2;
SET SESSION innodb_strict_mode = off;
CREATE TABLE t1(
@@ -202,18 +202,18 @@ create table t8 (id int primary key) eng
create table t9 (id int primary key) engine = innodb row_format = dynamic;
create table t10(id int primary key) engine = innodb row_format = compact;
create table t11(id int primary key) engine = innodb row_format = redundant;
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t1 Compact
-test t10 Compact
-test t11 Redundant
-test t3 Compressed
-test t4 Compressed
-test t5 Compressed
-test t6 Compressed
-test t7 Compressed
-test t8 Compressed
-test t9 Dynamic
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t1 Compact 16384 0
+test t10 Compact 16384 0
+test t11 Redundant 16384 0
+test t3 Compressed 1024 0
+test t4 Compressed 2048 0
+test t5 Compressed 4096 0
+test t6 Compressed 8192 0
+test t7 Compressed 16384 0
+test t8 Compressed 8192 0
+test t9 Dynamic 16384 0
drop table t1, t3, t4, t5, t6, t7, t8, t9, t10, t11;
create table t1 (id int primary key) engine = innodb
key_block_size = 8 row_format = compressed;
@@ -240,10 +240,10 @@ Warning 1478 InnoDB: cannot specify ROW_
Error 1005 Can't create table 'test.t4' (errno: 1478)
create table t5 (id int primary key) engine = innodb
key_block_size = 8 row_format = default;
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t1 Compressed
-test t5 Compressed
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t1 Compressed 8192 0
+test t5 Compressed 8192 0
drop table t1, t5;
create table t1 (id int primary key) engine = innodb
key_block_size = 9 row_format = redundant;
@@ -269,8 +269,8 @@ Level Code Message
Warning 1478 InnoDB: invalid KEY_BLOCK_SIZE = 9. Valid values are [1, 2, 4, 8, 16]
Warning 1478 InnoDB: cannot specify ROW_FORMAT = DYNAMIC with KEY_BLOCK_SIZE.
Error 1005 Can't create table 'test.t2' (errno: 1478)
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
set global innodb_file_per_table = off;
create table t1 (id int primary key) engine = innodb key_block_size = 1;
ERROR HY000: Can't create table 'test.t1' (errno: 1478)
@@ -316,10 +316,10 @@ Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC
Error 1005 Can't create table 'test.t7' (errno: 1478)
create table t8 (id int primary key) engine = innodb row_format = compact;
create table t9 (id int primary key) engine = innodb row_format = redundant;
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t8 Compact
-test t9 Redundant
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t8 Compact 16384 0
+test t9 Redundant 16384 0
drop table t8, t9;
set global innodb_file_per_table = on;
set global innodb_file_format = `0`;
@@ -367,10 +367,10 @@ Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC
Error 1005 Can't create table 'test.t7' (errno: 1478)
create table t8 (id int primary key) engine = innodb row_format = compact;
create table t9 (id int primary key) engine = innodb row_format = redundant;
-SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
-table_schema table_name row_format
-test t8 Compact
-test t9 Redundant
+SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';
+table_schema table_name row_format data_length index_length
+test t8 Compact 16384 0
+test t9 Redundant 16384 0
drop table t8, t9;
set global innodb_file_per_table=0;
set global innodb_file_format=Antelope;
=== modified file 'mysql-test/suite/innodb/t/innodb-zip.test'
--- a/mysql-test/suite/innodb/t/innodb-zip.test 2011-06-15 08:02:11 +0000
+++ b/mysql-test/suite/innodb/t/innodb-zip.test 2011-08-08 09:42:09 +0000
@@ -5,7 +5,7 @@ let $format=`select @@innodb_file_format
let $innodb_file_format_max_orig=`select @@innodb_file_format_max`;
let $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
--- let $query_i_s = SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'
+-- let $query_i_s = SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql'
set session innodb_strict_mode=0;
set global innodb_file_per_table=off;
=== modified file 'mysql-test/suite/opt_trace/include/general2.inc'
--- a/mysql-test/suite/opt_trace/include/general2.inc 2011-07-20 13:10:44 +0000
+++ b/mysql-test/suite/opt_trace/include/general2.inc 2011-07-21 19:58:23 +0000
@@ -398,3 +398,167 @@ table3.col_varchar_10_utf8_key
select * from information_schema.optimizer_trace;
DROP TABLE t1,t2,t3;
+
+--echo
+--echo Tests of tracing of the "eq_ref optimization" of plan search
+--echo
+
+# test for trace point "chosen:true","pruned_by_cost:true" and
+# "added_to_eq_ref_extension:true" (from main.subquery_sj_none_jcl7)
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, key(a));
+create table t2 (a int, b int, key(a));
+create table t3 (a int, b int, key(a));
+insert into t1 select a,a from t0;
+insert into t2 select a,a from t0;
+insert into t3 select a,a from t0;
+
+set @old_opt_switch=@@optimizer_switch;
+# The SET below must not be output, because only servers supporting
+# semijoin will execute it (would make varying output).
+if (`select locate('semijoin', @@optimizer_switch) > 0`)
+{
+--disable_query_log
+ set optimizer_switch="semijoin=off,materialization=off";
+--enable_query_log
+}
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
+
+select * from information_schema.optimizer_trace;
+set optimizer_switch=@old_opt_switch;
+drop table t0,t1,t2,t3;
+
+# test for trace point "added_to_eq_ref_extension:false" (from main.derived)
+
+CREATE TABLE t1 (
+OBJECTID int(11) NOT NULL default '0',
+SORTORDER int(11) NOT NULL auto_increment,
+KEY t1_SortIndex (SORTORDER),
+KEY t1_IdIndex (OBJECTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ID int(11) default NULL,
+PARID int(11) default NULL,
+UNIQUE KEY t2_ID_IDX (ID),
+KEY t2_PARID_IDX (PARID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
+CREATE TABLE t3 (
+ID int(11) default NULL,
+DATA decimal(10,2) default NULL,
+UNIQUE KEY t3_ID_IDX (ID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
+
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
+select * from information_schema.optimizer_trace;
+drop table t1,t2,t3;
+
+# test of multiple nested trace points "added_to_eq_ref_extension:true"
+# (eq_ref optimization finding a sequence of eq_ref-joined tables) (from
+# main.type_blob)
+
+CREATE TABLE t1 (
+t1_id bigint(21) NOT NULL auto_increment,
+_field_72 varchar(128) DEFAULT '' NOT NULL,
+_field_95 varchar(32),
+_field_115 tinyint(4) DEFAULT '0' NOT NULL,
+_field_122 tinyint(4) DEFAULT '0' NOT NULL,
+_field_126 tinyint(4),
+_field_134 tinyint(4),
+PRIMARY KEY (t1_id),
+UNIQUE _field_72 (_field_72),
+KEY _field_115 (_field_115),
+KEY _field_122 (_field_122)
+);
+INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
+
+CREATE TABLE t2 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t2 VALUES (2,1);
+INSERT INTO t2 VALUES (2,2);
+
+CREATE TABLE t3 (
+t3_id bigint(21) NOT NULL auto_increment,
+_field_131 varchar(128),
+_field_133 tinyint(4) DEFAULT '0' NOT NULL,
+_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_137 tinyint(4),
+_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_140 blob,
+_field_142 tinyint(4) DEFAULT '0' NOT NULL,
+_field_145 tinyint(4) DEFAULT '0' NOT NULL,
+_field_148 tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t3_id),
+KEY _field_133 (_field_133),
+KEY _field_135 (_field_135),
+KEY _field_139 (_field_139),
+KEY _field_142 (_field_142),
+KEY _field_145 (_field_145),
+KEY _field_148 (_field_148)
+);
+INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
+INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
+
+CREATE TABLE t4 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t4 VALUES (1,1);
+INSERT INTO t4 VALUES (2,1);
+
+CREATE TABLE t5 (
+t5_id bigint(21) NOT NULL auto_increment,
+_field_149 tinyint(4),
+_field_156 varchar(128) DEFAULT '' NOT NULL,
+_field_157 varchar(128) DEFAULT '' NOT NULL,
+_field_158 varchar(128) DEFAULT '' NOT NULL,
+_field_159 varchar(128) DEFAULT '' NOT NULL,
+_field_160 varchar(128) DEFAULT '' NOT NULL,
+_field_161 varchar(128) DEFAULT '' NOT NULL,
+PRIMARY KEY (t5_id),
+KEY _field_156 (_field_156),
+KEY _field_157 (_field_157),
+KEY _field_158 (_field_158),
+KEY _field_159 (_field_159),
+KEY _field_160 (_field_160),
+KEY _field_161 (_field_161)
+);
+INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
+INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
+
+CREATE TABLE t6 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t6 VALUES (1,1);
+INSERT INTO t6 VALUES (1,2);
+INSERT INTO t6 VALUES (2,2);
+
+CREATE TABLE t7 (
+t7_id bigint(21) NOT NULL auto_increment,
+_field_143 tinyint(4),
+_field_165 varchar(32),
+_field_166 smallint(6) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t7_id),
+KEY _field_166 (_field_166)
+);
+INSERT INTO t7 VALUES (1,0,'High',1);
+INSERT INTO t7 VALUES (2,0,'Medium',2);
+INSERT INTO t7 VALUES (3,0,'Low',3);
+
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
+select * from information_schema.optimizer_trace;
+drop table t1,t2,t3,t4,t5,t6,t7;
=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result 2011-07-20 13:10:44 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result 2011-07-21 19:58:23 +0000
@@ -2248,3 +2248,1723 @@ table3.col_varchar_10_utf8_key {
] /* steps */
} 0 0
DROP TABLE t1,t2,t3;
+
+Tests of tracing of the "eq_ref optimization" of plan search
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, key(a));
+create table t2 (a int, b int, key(a));
+create table t3 (a int, b int, key(a));
+insert into t1 select a,a from t0;
+insert into t2 select a,a from t0;
+insert into t3 select a,a from t0;
+set @old_opt_switch=@@optimizer_switch;
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select (`test`.`t2`.`a` + `test`.`t3`.`a`) from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))))"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true,
+ "evaluating_constant_where_conditions": [
+ ] /* evaluating_constant_where_conditions */
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_equality_propagation": [
+ ] /* subselect_equality_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t0",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t0",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.0171,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0171,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t0",
+ "attached": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t0",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4227,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`)))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`))"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "t3"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+set optimizer_switch=@old_opt_switch;
+drop table t0,t1,t2,t3;
+CREATE TABLE t1 (
+OBJECTID int(11) NOT NULL default '0',
+SORTORDER int(11) NOT NULL auto_increment,
+KEY t1_SortIndex (SORTORDER),
+KEY t1_IdIndex (OBJECTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ID int(11) default NULL,
+PARID int(11) default NULL,
+UNIQUE KEY t2_ID_IDX (ID),
+KEY t2_PARID_IDX (PARID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
+CREATE TABLE t3 (
+ID int(11) default NULL,
+DATA decimal(10,2) default NULL,
+UNIQUE KEY t3_ID_IDX (ID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
+497 ID NULL
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from ((`test`.`t1` join `test`.`t2` on((`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))) join `test`.`t3` on((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`))) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`) and (`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "t2_PARID_IDX"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 9,
+ "cost": 5.9198
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "t2_ID_IDX",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "t2_PARID_IDX",
+ "usable": true,
+ "key_parts": [
+ "PARID"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "const_keys_added": {
+ "keys": [
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 8,
+ "cost": 5.7195
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t3_ID_IDX",
+ "records": 0.8,
+ "cost": 0.96,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 8,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 0.96,
+ "records_for_plan": 0.8,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.88,
+ "records_for_plan": 1.6,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 0.9,
+ "cost": 1.08,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.08,
+ "records_for_plan": 0.9,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 497 AS `497`,`tmp`.`ID` AS `ID`,NULL AS `NULL` from (/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from `test`.`t1` join `test`.`t2` join `test`.`t3` where multiple equal(NULL, `test`.`t3`.`ID`, `test`.`t2`.`ID`) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc) `tmp`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "tmp",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3;
+CREATE TABLE t1 (
+t1_id bigint(21) NOT NULL auto_increment,
+_field_72 varchar(128) DEFAULT '' NOT NULL,
+_field_95 varchar(32),
+_field_115 tinyint(4) DEFAULT '0' NOT NULL,
+_field_122 tinyint(4) DEFAULT '0' NOT NULL,
+_field_126 tinyint(4),
+_field_134 tinyint(4),
+PRIMARY KEY (t1_id),
+UNIQUE _field_72 (_field_72),
+KEY _field_115 (_field_115),
+KEY _field_122 (_field_122)
+);
+INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
+CREATE TABLE t2 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t2 VALUES (2,1);
+INSERT INTO t2 VALUES (2,2);
+CREATE TABLE t3 (
+t3_id bigint(21) NOT NULL auto_increment,
+_field_131 varchar(128),
+_field_133 tinyint(4) DEFAULT '0' NOT NULL,
+_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_137 tinyint(4),
+_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_140 blob,
+_field_142 tinyint(4) DEFAULT '0' NOT NULL,
+_field_145 tinyint(4) DEFAULT '0' NOT NULL,
+_field_148 tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t3_id),
+KEY _field_133 (_field_133),
+KEY _field_135 (_field_135),
+KEY _field_139 (_field_139),
+KEY _field_142 (_field_142),
+KEY _field_145 (_field_145),
+KEY _field_148 (_field_148)
+);
+INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
+INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
+CREATE TABLE t4 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t4 VALUES (1,1);
+INSERT INTO t4 VALUES (2,1);
+CREATE TABLE t5 (
+t5_id bigint(21) NOT NULL auto_increment,
+_field_149 tinyint(4),
+_field_156 varchar(128) DEFAULT '' NOT NULL,
+_field_157 varchar(128) DEFAULT '' NOT NULL,
+_field_158 varchar(128) DEFAULT '' NOT NULL,
+_field_159 varchar(128) DEFAULT '' NOT NULL,
+_field_160 varchar(128) DEFAULT '' NOT NULL,
+_field_161 varchar(128) DEFAULT '' NOT NULL,
+PRIMARY KEY (t5_id),
+KEY _field_156 (_field_156),
+KEY _field_157 (_field_157),
+KEY _field_158 (_field_158),
+KEY _field_159 (_field_159),
+KEY _field_160 (_field_160),
+KEY _field_161 (_field_161)
+);
+INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
+INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
+CREATE TABLE t6 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t6 VALUES (1,1);
+INSERT INTO t6 VALUES (1,2);
+INSERT INTO t6 VALUES (2,2);
+CREATE TABLE t7 (
+t7_id bigint(21) NOT NULL auto_increment,
+_field_143 tinyint(4),
+_field_165 varchar(32),
+_field_166 smallint(6) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t7_id),
+KEY _field_166 (_field_166)
+);
+INSERT INTO t7 VALUES (1,0,'High',1);
+INSERT INTO t7 VALUES (2,0,'Medium',2);
+INSERT INTO t7 VALUES (3,0,'Low',3);
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
+replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id
+test^M
+job^M
+1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
+job^M
+1 1
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select replace(`test`.`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id`,min(`test`.`t3`.`_field_131`) AS `min(t3._field_131)`,min(`test`.`t3`.`_field_135`) AS `min(t3._field_135)`,min(`test`.`t3`.`_field_139`) AS `min(t3._field_139)`,min(`test`.`t3`.`_field_137`) AS `min(t3._field_137)`,min(`test`.`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`test`.`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`test`.`t3`.`_field_145`) AS `min(t3._field_145)`,min(`test`.`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`test`.`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id` from ((((((`test`.`t3` left join `test`.`t4` on((`test`.`t4`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t7` `link_alias_142` on((`test`.`t4`.`seq_1_id` = `test`.`link_alias!
_142`.`t7_id`))) left join `test`.`t6` on((`test`.`t6`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t1` `link_alias_133` on((`test`.`t6`.`seq_1_id` = `test`.`link_alias_133`.`t1_id`))) left join `test`.`t2` on((`test`.`t2`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t5` `link_alias_148` on((`test`.`t2`.`seq_1_id` = `test`.`link_alias_148`.`t5_id`))) where (`test`.`t3`.`t3_id` = 1) group by `test`.`t3`.`t3_id` order by `test`.`link_alias_142`.`_field_166`,`test`.`t3`.`_field_139`,`test`.`link_alias_133`.`_field_72`,`test`.`t3`.`_field_135`,`test`.`link_alias_148`.`_field_156`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t3`.`t3_id` = 1)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "t3_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "field": "t7_id",
+ "equals": "`test`.`t4`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "field": "t1_id",
+ "equals": "`test`.`t6`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "field": "t5_id",
+ "equals": "`test`.`t2`.`seq_1_id`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "const"
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5083
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.4435,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7156
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t7_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_166",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.741
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t1_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_72",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_115",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_122",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5137
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t5_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_156",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_157",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_158",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_159",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_160",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_161",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t4",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.2,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 2.4,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 3.6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 4.8,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 7.2,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "1",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t4",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t4"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142"
+ },
+ {
+ "database": "test",
+ "table": "t6"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3,t4,t5,t6,t7;
=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2011-07-20 13:10:44 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2011-07-21 19:58:23 +0000
@@ -2301,3 +2301,1723 @@ table3.col_varchar_10_utf8_key {
] /* steps */
} 0 0
DROP TABLE t1,t2,t3;
+
+Tests of tracing of the "eq_ref optimization" of plan search
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, key(a));
+create table t2 (a int, b int, key(a));
+create table t3 (a int, b int, key(a));
+insert into t1 select a,a from t0;
+insert into t2 select a,a from t0;
+insert into t3 select a,a from t0;
+set @old_opt_switch=@@optimizer_switch;
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+explain select *
+from t0 where a in
+(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select (`test`.`t2`.`a` + `test`.`t3`.`a`) from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))))"
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "semijoin",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "chosen": false
+ } /* transformation */
+ },
+ {
+ "transformation": {
+ "select#": 2,
+ "from": "IN (SELECT)",
+ "to": "EXISTS (CORRELATED SELECT)",
+ "chosen": true,
+ "evaluating_constant_where_conditions": [
+ ] /* evaluating_constant_where_conditions */
+ } /* transformation */
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_equality_propagation": [
+ ] /* subselect_equality_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_constant_propagation": [
+ ] /* subselect_constant_propagation */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_cond_removal": [
+ ] /* subselect_cond_removal */,
+ "resulting_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t0",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t0",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.0171,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.0171,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t0",
+ "attached": "<in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from (`test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)))) where (<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`))))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t0",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((<cache>(`test`.`t0`.`a`) = (`test`.`t2`.`a` + `test`.`t3`.`a`)) and multiple equal(`test`.`t2`.`a`, `test`.`t1`.`a`, `test`.`t3`.`a`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "a",
+ "equals": "`test`.`t3`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t2`.`a`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "a",
+ "equals": "`test`.`t1`.`a`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "table_scan": {
+ "records": 10,
+ "cost": 2
+ } /* table_scan */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4227,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "usable": false,
+ "chosen": false
+ },
+ {
+ "access_type": "scan",
+ "records": 10,
+ "cost": 2.022,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4237,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 28.022,
+ "records_for_plan": 10,
+ "pruned_by_cost": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "a",
+ "records": 1,
+ "cost": 10.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "using_join_cache": true,
+ "records": 8,
+ "cost": 2.4229,
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 16.022,
+ "records_for_plan": 10,
+ "pruned_by_heuristic": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`)))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "(<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`))"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1",
+ "scan_type": "index"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "t3"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+set optimizer_switch=@old_opt_switch;
+drop table t0,t1,t2,t3;
+CREATE TABLE t1 (
+OBJECTID int(11) NOT NULL default '0',
+SORTORDER int(11) NOT NULL auto_increment,
+KEY t1_SortIndex (SORTORDER),
+KEY t1_IdIndex (OBJECTID)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ID int(11) default NULL,
+PARID int(11) default NULL,
+UNIQUE KEY t2_ID_IDX (ID),
+KEY t2_PARID_IDX (PARID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
+CREATE TABLE t3 (
+ID int(11) default NULL,
+DATA decimal(10,2) default NULL,
+UNIQUE KEY t3_ID_IDX (ID)
+) engine=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
+497 ID NULL
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from ((`test`.`t1` join `test`.`t2` on((`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))) join `test`.`t3` on((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`))) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`OBJECTID` = `test`.`t3`.`ID`) and (`test`.`t1`.`OBJECTID` = `test`.`t2`.`ID`))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(`test`.`t1`.`OBJECTID`, `test`.`t3`.`ID`, `test`.`t2`.`ID`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "OBJECTID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "ID",
+ "equals": "`test`.`t3`.`ID`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t1`.`OBJECTID`",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "ID",
+ "equals": "`test`.`t2`.`ID`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "const_keys_added": {
+ "keys": [
+ "t2_PARID_IDX"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 9,
+ "cost": 5.9198
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "t2_ID_IDX",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "t2_PARID_IDX",
+ "usable": true,
+ "key_parts": [
+ "PARID"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t3",
+ "const_keys_added": {
+ "keys": [
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 8,
+ "cost": 5.7195
+ } /* table_scan */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t3",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t3_ID_IDX",
+ "records": 0.8,
+ "cost": 0.96,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 8,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 0.96,
+ "records_for_plan": 0.8,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": false
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 2,
+ "cost": 2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 2.88,
+ "records_for_plan": 1.6,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "t2_ID_IDX",
+ "records": 0.9,
+ "cost": 1.08,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 9,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.08,
+ "records_for_plan": 0.9,
+ "pruned_by_heuristic": true
+ }
+ ] /* considered_execution_plans */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ },
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select 497 AS `497`,`tmp`.`ID` AS `ID`,NULL AS `NULL` from (/* select#2 */ select 497 AS `ID`,max(`test`.`t3`.`DATA`) AS `DATA` from `test`.`t1` join `test`.`t2` join `test`.`t3` where multiple equal(NULL, `test`.`t3`.`ID`, `test`.`t2`.`ID`) group by `test`.`t2`.`PARID` order by max(`test`.`t3`.`DATA`) desc) `tmp`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "records_estimation": [
+ {
+ "database": "",
+ "table": "tmp",
+ "records": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* records_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3;
+CREATE TABLE t1 (
+t1_id bigint(21) NOT NULL auto_increment,
+_field_72 varchar(128) DEFAULT '' NOT NULL,
+_field_95 varchar(32),
+_field_115 tinyint(4) DEFAULT '0' NOT NULL,
+_field_122 tinyint(4) DEFAULT '0' NOT NULL,
+_field_126 tinyint(4),
+_field_134 tinyint(4),
+PRIMARY KEY (t1_id),
+UNIQUE _field_72 (_field_72),
+KEY _field_115 (_field_115),
+KEY _field_122 (_field_122)
+);
+INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
+INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
+CREATE TABLE t2 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t2 VALUES (2,1);
+INSERT INTO t2 VALUES (2,2);
+CREATE TABLE t3 (
+t3_id bigint(21) NOT NULL auto_increment,
+_field_131 varchar(128),
+_field_133 tinyint(4) DEFAULT '0' NOT NULL,
+_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_137 tinyint(4),
+_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
+_field_140 blob,
+_field_142 tinyint(4) DEFAULT '0' NOT NULL,
+_field_145 tinyint(4) DEFAULT '0' NOT NULL,
+_field_148 tinyint(4) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t3_id),
+KEY _field_133 (_field_133),
+KEY _field_135 (_field_135),
+KEY _field_139 (_field_139),
+KEY _field_142 (_field_142),
+KEY _field_145 (_field_145),
+KEY _field_148 (_field_148)
+);
+INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
+INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
+CREATE TABLE t4 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t4 VALUES (1,1);
+INSERT INTO t4 VALUES (2,1);
+CREATE TABLE t5 (
+t5_id bigint(21) NOT NULL auto_increment,
+_field_149 tinyint(4),
+_field_156 varchar(128) DEFAULT '' NOT NULL,
+_field_157 varchar(128) DEFAULT '' NOT NULL,
+_field_158 varchar(128) DEFAULT '' NOT NULL,
+_field_159 varchar(128) DEFAULT '' NOT NULL,
+_field_160 varchar(128) DEFAULT '' NOT NULL,
+_field_161 varchar(128) DEFAULT '' NOT NULL,
+PRIMARY KEY (t5_id),
+KEY _field_156 (_field_156),
+KEY _field_157 (_field_157),
+KEY _field_158 (_field_158),
+KEY _field_159 (_field_159),
+KEY _field_160 (_field_160),
+KEY _field_161 (_field_161)
+);
+INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
+INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
+CREATE TABLE t6 (
+seq_0_id bigint(21) DEFAULT '0' NOT NULL,
+seq_1_id bigint(21) DEFAULT '0' NOT NULL,
+PRIMARY KEY (seq_0_id,seq_1_id)
+);
+INSERT INTO t6 VALUES (1,1);
+INSERT INTO t6 VALUES (1,2);
+INSERT INTO t6 VALUES (2,2);
+CREATE TABLE t7 (
+t7_id bigint(21) NOT NULL auto_increment,
+_field_143 tinyint(4),
+_field_165 varchar(32),
+_field_166 smallint(6) DEFAULT '0' NOT NULL,
+PRIMARY KEY (t7_id),
+KEY _field_166 (_field_166)
+);
+INSERT INTO t7 VALUES (1,0,'High',1);
+INSERT INTO t7 VALUES (2,0,'Medium',2);
+INSERT INTO t7 VALUES (3,0,'Low',3);
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
+replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id
+test^M
+job^M
+1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
+job^M
+1 1
+select * from information_schema.optimizer_trace;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select replace(`test`.`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id`,min(`test`.`t3`.`_field_131`) AS `min(t3._field_131)`,min(`test`.`t3`.`_field_135`) AS `min(t3._field_135)`,min(`test`.`t3`.`_field_139`) AS `min(t3._field_139)`,min(`test`.`t3`.`_field_137`) AS `min(t3._field_137)`,min(`test`.`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`test`.`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`test`.`t3`.`_field_145`) AS `min(t3._field_145)`,min(`test`.`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`test`.`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`test`.`t3`.`t3_id` AS `t3_id` from ((((((`test`.`t3` left join `test`.`t4` on((`test`.`t4`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t7` `link_alias_142` on((`test`.`t4`.`seq_1_id` = `test`.`link_alias!
_142`.`t7_id`))) left join `test`.`t6` on((`test`.`t6`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t1` `link_alias_133` on((`test`.`t6`.`seq_1_id` = `test`.`link_alias_133`.`t1_id`))) left join `test`.`t2` on((`test`.`t2`.`seq_0_id` = `test`.`t3`.`t3_id`))) left join `test`.`t5` `link_alias_148` on((`test`.`t2`.`seq_1_id` = `test`.`link_alias_148`.`t5_id`))) where (`test`.`t3`.`t3_id` = 1) group by `test`.`t3`.`t3_id` order by `test`.`link_alias_142`.`_field_166`,`test`.`t3`.`_field_139`,`test`.`link_alias_133`.`_field_72`,`test`.`t3`.`_field_135`,`test`.`link_alias_148`.`_field_156`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(`test`.`t3`.`t3_id` = 1)",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "multiple equal(1, `test`.`t3`.`t3_id`)"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t3",
+ "field": "t3_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "field": "t7_id",
+ "equals": "`test`.`t4`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "field": "t1_id",
+ "equals": "`test`.`t6`.`seq_1_id`",
+ "null_rejecting": true
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "field": "seq_0_id",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "field": "t5_id",
+ "equals": "`test`.`t2`.`seq_1_id`",
+ "null_rejecting": true
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t3",
+ "records": 1,
+ "cost": 1,
+ "table_type": "const"
+ },
+ {
+ "database": "test",
+ "table": "t4",
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5083
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.4435,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7156
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t7_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_166",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.741
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t1_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_72",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_115",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_122",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 3,
+ "cost": 4.7125
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "seq_0_id",
+ "seq_1_id"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "PRIMARY",
+ "cost": 1.687,
+ "chosen": true
+ } /* best_covering_index_scan */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": false,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "usable": false,
+ "cause": "too_few_roworder_scans"
+ } /* analyzing_roworder_intersect */
+ } /* analyzing_range_alternatives */
+ } /* range_analysis */
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "const_keys_added": {
+ "keys": [
+ "PRIMARY"
+ ] /* keys */,
+ "cause": "group_by"
+ } /* const_keys_added */,
+ "range_analysis": {
+ "table_scan": {
+ "records": 2,
+ "cost": 4.5137
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "t5_id"
+ ] /* key_parts */
+ },
+ {
+ "index": "_field_156",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_157",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_158",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_159",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_160",
+ "usable": false,
+ "cause": "not_applicable"
+ },
+ {
+ "index": "_field_161",
+ "usable": false,
+ "cause": "not_applicable"
+ }
+ ] /* potential_range_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_single_table"
+ } /* group_index_range */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t4",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 1.2,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 2.4,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t6",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 3.6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 4.8,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 3,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 6,
+ "records_for_plan": 1,
+ "rest_of_plan": [
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "ref",
+ "index": "PRIMARY",
+ "records": 1,
+ "cost": 1.2,
+ "chosen": true
+ },
+ {
+ "access_type": "scan",
+ "cost": 2,
+ "records": 2,
+ "cause": "cost",
+ "chosen": false
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "added_to_eq_ref_extension": true,
+ "cost_for_plan": 7.2,
+ "records_for_plan": 1,
+ "chosen": true
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* rest_of_plan */
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "1",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t4",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t6",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": null
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148",
+ "attached": null
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t4"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_142"
+ },
+ {
+ "database": "test",
+ "table": "t6"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_133"
+ },
+ {
+ "database": "test",
+ "table": "t2"
+ },
+ {
+ "database": "test",
+ "table": "link_alias_148"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+drop table t1,t2,t3,t4,t5,t6,t7;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-08-02 08:14:26 +0000
+++ b/sql/sql_select.cc 2011-08-05 10:26:13 +0000
@@ -8967,6 +8967,7 @@ bool Optimize_table_order::best_extensio
if (eq_ref_extended == (table_map)0)
{
/* Try an EQ_REF-joined expansion of the partial plan */
+ Opt_trace_array trace_rest(trace, "rest_of_plan");
eq_ref_extended= real_table_bit |
eq_ref_extension_by_limited_search(
remaining_tables & ~real_table_bit,
@@ -8989,6 +8990,7 @@ bool Optimize_table_order::best_extensio
read_time,
current_read_time,
"pruned_by_eq_ref_heuristic"););
+ trace_one_table.add("pruned_by_eq_ref_heuristic", true);
backout_nj_sj_state(remaining_tables, s);
continue;
}
@@ -9142,151 +9144,164 @@ table_map Optimize_table_order::eq_ref_e
{
DBUG_ENTER("Optimize_table_order::eq_ref_extension_by_limited_search");
+ if (remaining_tables == 0)
+ DBUG_RETURN(0);
+
const bool has_sj=
!(join->select_lex->sj_nests.is_empty() || join->emb_sjm_nest);
/*
- The brackeded section below add 'eq_ref' joinable tables to
- the QEP in the order they are found in the 'remaining_tables' set.
+ The section below adds 'eq_ref' joinable tables to the QEP in the order
+ they are found in the 'remaining_tables' set.
See above description for why we can add these without greedy
cost analysis.
*/
- if (remaining_tables)
+ Opt_trace_context * const trace= &thd->opt_trace;
+ table_map eq_ref_ext(0);
+ JOIN_TAB *s;
+ JOIN_TAB *saved_refs[MAX_TABLES];
+ // Save 'best_ref[]' as we has to restore before return.
+ memcpy(saved_refs, join->best_ref + idx,
+ sizeof(JOIN_TAB*) * (join->tables-idx));
+
+ for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
{
- table_map eq_ref_ext(0);
- JOIN_TAB *s;
- JOIN_TAB *saved_refs[MAX_TABLES];
- // Save 'best_ref[]' as we has to restore before return.
- memcpy(saved_refs, join->best_ref + idx,
- sizeof(JOIN_TAB*) * (join->tables-idx));
+ const table_map real_table_bit= s->table->map;
- for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
- {
- const table_map real_table_bit= s->table->map;
+ /*
+ Don't move swap inside conditional code: All items
+ should be swapped to maintain '#rows' ordered tables.
+ This is critical for early pruning of bad plans.
+ */
+ swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
- /*
- Don't move swap inside conditional code: All items
- should be swapped to maintain '#rows' ordered tables.
- This is critical for early pruning of bad plans.
- */
- swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
+ /*
+ Consider table for 'eq_ref' heuristic if:
+ 1) It might use a keyref for best_access_path
+ 2) and, Table remains to be handled.
+ 3) and, It is independent of those not yet in partial plan.
+ 4) and, It passed the interleaving check.
+ */
+ if (s->keyuse && // 1)
+ (remaining_tables & real_table_bit) && // 2)
+ !(remaining_tables & s->dependent) && // 3)
+ (!idx || !check_interleaving_with_nj(s))) // 4)
+ {
+ Opt_trace_object trace_one_table(trace);
+ trace_one_table.add_utf8_table(s->table);
+ POSITION *const position= join->positions + idx;
+ POSITION loose_scan_pos;
+
+ /* Find the best access method from 's' to the current partial plan */
+ best_access_path(join, s, excluded_tables | remaining_tables,
+ idx, FALSE, record_count,
+ position, &loose_scan_pos);
/*
- Consider table for 'eq_ref' heuristic if:
- 1) It might use a keyref for best_access_path
- 2) and, Table remains to be handled.
- 3) and, It is independent of those not yet in partial plan.
- 4) and, It passed the interleaving check.
- */
- if (s->keyuse && // 1)
- (remaining_tables & real_table_bit) && // 2)
- !(remaining_tables & s->dependent) && // 3)
- (!idx || !check_interleaving_with_nj(s))) // 4)
- {
- POSITION *const position= join->positions + idx;
- POSITION loose_scan_pos;
-
- /* Find the best access method from 's' to the current partial plan */
- best_access_path(join, s, excluded_tables | remaining_tables,
- idx, FALSE, record_count,
- position, &loose_scan_pos);
-
- /* EQ_REF prune logic is based on that all joins
- in the ref_extension has the same #rows and cost.
- -> The total cost of the QEP is independent of the order
+ EQ_REF prune logic is based on that all joins
+ in the ref_extension has the same #rows and cost.
+ -> The total cost of the QEP is independent of the order
of joins within this 'ref_extension'.
- Expand QEP with all 'identical' REFs in
+ Expand QEP with all 'identical' REFs in
'join->positions' order.
- */
- if (position->key &&
- position->read_time == (position-1)->read_time &&
- position->records_read == (position-1)->records_read)
- {
- double current_record_count, current_read_time;
-
- /* Add the cost of extending the plan with 's' */
- current_record_count= record_count * position->records_read;
- current_read_time= read_time
- + position->read_time
- + current_record_count * ROW_EVALUATE_COST;
+ */
+ const bool added_to_eq_ref_extension=
+ position->key &&
+ position->read_time == (position-1)->read_time &&
+ position->records_read == (position-1)->records_read;
+ trace_one_table.add("added_to_eq_ref_extension",
+ added_to_eq_ref_extension);
+ if (added_to_eq_ref_extension)
+ {
+ double current_record_count, current_read_time;
- if (has_sj)
- {
- /*
- Even if there are no semijoins, advance_sj_state() has a
- significant cost (takes 9% of time in a 20-table plan search),
- hence the if() above, which is also more efficient than the
- same if() inside advance_sj_state() would be.
- */
- advance_sj_state(remaining_tables, s, idx,
- ¤t_record_count, ¤t_read_time,
- &loose_scan_pos);
- }
- else
- position->sj_strategy= SJ_OPT_NONE;
+ /* Add the cost of extending the plan with 's' */
+ current_record_count= record_count * position->records_read;
+ current_read_time= read_time
+ + position->read_time
+ + current_record_count * ROW_EVALUATE_COST;
- /* Expand only partial plans with lower cost than the best QEP so far */
- if (current_read_time >= join->best_read)
- {
- DBUG_EXECUTE("opt", print_plan(join, idx+1,
- current_record_count,
- read_time,
- current_read_time,
- "prune_by_cost"););
- backout_nj_sj_state(remaining_tables, s);
- continue;
- }
+ trace_one_table.add("cost_for_plan", current_read_time).
+ add("records_for_plan", current_record_count);
- eq_ref_ext= real_table_bit;
- if ((current_search_depth > 1) && (remaining_tables & ~real_table_bit))
- {
- DBUG_EXECUTE("opt", print_plan(join, idx + 1,
- current_record_count,
- read_time,
- current_read_time,
- "EQ_REF_extension"););
+ if (has_sj)
+ {
+ /*
+ Even if there are no semijoins, advance_sj_state() has a
+ significant cost (takes 9% of time in a 20-table plan search),
+ hence the if() above, which is also more efficient than the
+ same if() inside advance_sj_state() would be.
+ */
+ advance_sj_state(remaining_tables, s, idx,
+ ¤t_record_count, ¤t_read_time,
+ &loose_scan_pos);
+ }
+ else
+ position->sj_strategy= SJ_OPT_NONE;
- /* Recursively EQ_REF-extend the current partial plan */
- eq_ref_ext|=
- eq_ref_extension_by_limited_search(remaining_tables & ~real_table_bit,
- idx + 1,
- current_record_count,
- current_read_time,
- current_search_depth - 1);
- }
- else
- {
- plan_is_complete(idx, current_record_count, current_read_time);
- // TODO(didrik): add trace here.
- // trace_one_table.add("chosen", true);
- }
+ // Expand only partial plans with lower cost than the best QEP so far
+ if (current_read_time >= join->best_read)
+ {
+ DBUG_EXECUTE("opt", print_plan(join, idx+1,
+ current_record_count,
+ read_time,
+ current_read_time,
+ "prune_by_cost"););
+ trace_one_table.add("pruned_by_cost", true);
backout_nj_sj_state(remaining_tables, s);
- memcpy(join->best_ref + idx, saved_refs, sizeof(JOIN_TAB*) * (join->tables-idx));
- DBUG_RETURN(eq_ref_ext);
- } // if ('is eq_ref')
+ continue;
+ }
+
+ eq_ref_ext= real_table_bit;
+ if ((current_search_depth > 1) &&
+ (remaining_tables & ~real_table_bit))
+ {
+ DBUG_EXECUTE("opt", print_plan(join, idx + 1,
+ current_record_count,
+ read_time,
+ current_read_time,
+ "EQ_REF_extension"););
+ /* Recursively EQ_REF-extend the current partial plan */
+ Opt_trace_array trace_rest(trace, "rest_of_plan");
+ eq_ref_ext|=
+ eq_ref_extension_by_limited_search(remaining_tables &
+ ~real_table_bit,
+ idx + 1,
+ current_record_count,
+ current_read_time,
+ current_search_depth - 1);
+ }
+ else
+ {
+ plan_is_complete(idx, current_record_count, current_read_time);
+ trace_one_table.add("chosen", true);
+ }
backout_nj_sj_state(remaining_tables, s);
- } // check_interleaving_with_nj()
- } // for (...)
+ memcpy(join->best_ref + idx, saved_refs,
+ sizeof(JOIN_TAB*) * (join->tables - idx));
+ DBUG_RETURN(eq_ref_ext);
+ } // if (added_to_eq_ref_extension)
- memcpy(join->best_ref + idx, saved_refs, sizeof(JOIN_TAB*) * (join->tables-idx));
- /*
- 'eq_ref' heuristc didn't find a table to be appended to
- the query plan. We need to use the greedy search
- for finding the next table to be added.
- */
- DBUG_ASSERT(!eq_ref_ext);
- if (best_extension_by_limited_search(remaining_tables,
- idx,
- record_count,
- read_time,
- current_search_depth))
- DBUG_RETURN(~(table_map)0);
+ backout_nj_sj_state(remaining_tables, s);
+ } // if (... !check_interleaving_with_nj() ...)
+ } // for (JOIN_TAB **pos= ...)
- DBUG_RETURN(eq_ref_ext);
- }
+ memcpy(join->best_ref + idx, saved_refs, sizeof(JOIN_TAB*) * (join->tables-idx));
+ /*
+ 'eq_ref' heuristc didn't find a table to be appended to
+ the query plan. We need to use the greedy search
+ for finding the next table to be added.
+ */
+ DBUG_ASSERT(!eq_ref_ext);
+ if (best_extension_by_limited_search(remaining_tables,
+ idx,
+ record_count,
+ read_time,
+ current_search_depth))
+ DBUG_RETURN(~(table_map)0);
- DBUG_RETURN(0);
+ DBUG_RETURN(eq_ref_ext);
}
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc 2011-08-04 14:33:12 +0000
+++ b/storage/innobase/handler/ha_innodb.cc 2011-08-08 09:42:09 +0000
@@ -8393,6 +8393,8 @@ ha_innobase::info_low(
if (flag & HA_STATUS_VARIABLE) {
+ ulint page_size;
+
n_rows = ib_table->stat_n_rows;
/* Because we do not protect stat_n_rows by any mutex in a
@@ -8433,14 +8435,19 @@ ha_innobase::info_low(
prebuilt->autoinc_last_value = 0;
}
+ page_size = dict_table_zip_size(ib_table);
+ if (page_size == 0) {
+ page_size = UNIV_PAGE_SIZE;
+ }
+
stats.records = (ha_rows)n_rows;
stats.deleted = 0;
- stats.data_file_length = ((ulonglong)
- ib_table->stat_clustered_index_size)
- * UNIV_PAGE_SIZE;
- stats.index_file_length = ((ulonglong)
- ib_table->stat_sum_of_other_index_sizes)
- * UNIV_PAGE_SIZE;
+ stats.data_file_length
+ = ((ulonglong) ib_table->stat_clustered_index_size)
+ * page_size;
+ stats.index_file_length =
+ ((ulonglong) ib_table->stat_sum_of_other_index_sizes)
+ * page_size;
/* Since fsp_get_available_space_in_free_extents() is
acquiring latches inside InnoDB, we do not call it if we
=== modified file 'storage/perfschema/pfs_instr.cc'
--- a/storage/perfschema/pfs_instr.cc 2011-08-10 06:26:45 +0000
+++ b/storage/perfschema/pfs_instr.cc 2011-08-10 07:06:02 +0000
@@ -567,34 +567,52 @@ void PFS_scan::init(uint random, uint ma
*/
PFS_mutex* create_mutex(PFS_mutex_class *klass, const void *identity)
{
- PFS_scan scan;
- uint random= randomized_index(identity, mutex_max);
+ static uint mutex_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+ PFS_mutex *pfs;
+
+ while (++attempts <= mutex_max)
+ {
+ /*
+ Problem:
+ Multiple threads running concurrently may need to create a new
+ instrumented mutex, and find an empty slot in mutex_array[].
+ With N1 threads running on a N2 core hardware:
+ - up to N2 hardware threads can run concurrently,
+ causing contention if looking at the same array[i] slot.
+ - up to N1 threads can run almost concurrently (with thread scheduling),
+ scanning maybe overlapping regions in the [0-mutex_max] array.
+
+ Solution:
+ Instead of letting different threads compete on the same array[i] entry,
+ this code forces all threads to cooperate with the monotonic_index.
+ Only one thread will be allowed to test a given array[i] slot.
+ All threads do scan from the same region, starting at monotonic_index.
+ Serializing on monotonic_index ensures that when a slot is found occupied
+ in a given loop by a given thread, other threads will not attempt this
+ slot.
+ */
+ PFS_atomic::add_u32(& mutex_monotonic_index, 1);
+ index= mutex_monotonic_index % mutex_max;
+ pfs= mutex_array + index;
- for (scan.init(random, mutex_max);
- scan.has_pass();
- scan.next_pass())
- {
- PFS_mutex *pfs= mutex_array + scan.first();
- PFS_mutex *pfs_last= mutex_array + scan.last();
- for ( ; pfs < pfs_last; pfs++)
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
- {
- pfs->m_identity= identity;
- pfs->m_class= klass;
- pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
- pfs->m_timed= klass->m_timed;
- pfs->m_wait_stat.reset();
- pfs->m_lock_stat.reset();
- pfs->m_owner= NULL;
- pfs->m_last_locked= 0;
- pfs->m_lock.dirty_to_allocated();
- if (klass->is_singleton())
- klass->m_singleton= pfs;
- return pfs;
- }
+ pfs->m_identity= identity;
+ pfs->m_class= klass;
+ pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
+ pfs->m_timed= klass->m_timed;
+ pfs->m_wait_stat.reset();
+ pfs->m_lock_stat.reset();
+ pfs->m_owner= NULL;
+ pfs->m_last_locked= 0;
+ pfs->m_lock.dirty_to_allocated();
+ if (klass->is_singleton())
+ klass->m_singleton= pfs;
+ return pfs;
}
}
}
@@ -628,37 +646,37 @@ void destroy_mutex(PFS_mutex *pfs)
*/
PFS_rwlock* create_rwlock(PFS_rwlock_class *klass, const void *identity)
{
- PFS_scan scan;
- uint random= randomized_index(identity, rwlock_max);
+ static uint rwlock_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+ PFS_rwlock *pfs;
- for (scan.init(random, rwlock_max);
- scan.has_pass();
- scan.next_pass())
- {
- PFS_rwlock *pfs= rwlock_array + scan.first();
- PFS_rwlock *pfs_last= rwlock_array + scan.last();
- for ( ; pfs < pfs_last; pfs++)
+ while (++attempts <= rwlock_max)
+ {
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& rwlock_monotonic_index, 1);
+ index= rwlock_monotonic_index % rwlock_max;
+ pfs= rwlock_array + index;
+
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
- {
- pfs->m_identity= identity;
- pfs->m_class= klass;
- pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
- pfs->m_timed= klass->m_timed;
- pfs->m_wait_stat.reset();
- pfs->m_lock.dirty_to_allocated();
- pfs->m_read_lock_stat.reset();
- pfs->m_write_lock_stat.reset();
- pfs->m_writer= NULL;
- pfs->m_readers= 0;
- pfs->m_last_written= 0;
- pfs->m_last_read= 0;
- if (klass->is_singleton())
- klass->m_singleton= pfs;
- return pfs;
- }
+ pfs->m_identity= identity;
+ pfs->m_class= klass;
+ pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
+ pfs->m_timed= klass->m_timed;
+ pfs->m_wait_stat.reset();
+ pfs->m_lock.dirty_to_allocated();
+ pfs->m_read_lock_stat.reset();
+ pfs->m_write_lock_stat.reset();
+ pfs->m_writer= NULL;
+ pfs->m_readers= 0;
+ pfs->m_last_written= 0;
+ pfs->m_last_read= 0;
+ if (klass->is_singleton())
+ klass->m_singleton= pfs;
+ return pfs;
}
}
}
@@ -692,33 +710,33 @@ void destroy_rwlock(PFS_rwlock *pfs)
*/
PFS_cond* create_cond(PFS_cond_class *klass, const void *identity)
{
- PFS_scan scan;
- uint random= randomized_index(identity, cond_max);
+ static uint cond_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+ PFS_cond *pfs;
+
+ while (++attempts <= cond_max)
+ {
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& cond_monotonic_index, 1);
+ index= cond_monotonic_index % cond_max;
+ pfs= cond_array + index;
- for (scan.init(random, cond_max);
- scan.has_pass();
- scan.next_pass())
- {
- PFS_cond *pfs= cond_array + scan.first();
- PFS_cond *pfs_last= cond_array + scan.last();
- for ( ; pfs < pfs_last; pfs++)
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
- {
- pfs->m_identity= identity;
- pfs->m_class= klass;
- pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
- pfs->m_timed= klass->m_timed;
- pfs->m_cond_stat.m_signal_count= 0;
- pfs->m_cond_stat.m_broadcast_count= 0;
- pfs->m_wait_stat.reset();
- pfs->m_lock.dirty_to_allocated();
- if (klass->is_singleton())
- klass->m_singleton= pfs;
- return pfs;
- }
+ pfs->m_identity= identity;
+ pfs->m_class= klass;
+ pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
+ pfs->m_timed= klass->m_timed;
+ pfs->m_cond_stat.m_signal_count= 0;
+ pfs->m_cond_stat.m_broadcast_count= 0;
+ pfs->m_wait_stat.reset();
+ pfs->m_lock.dirty_to_allocated();
+ if (klass->is_singleton())
+ klass->m_singleton= pfs;
+ return pfs;
}
}
}
@@ -762,124 +780,123 @@ PFS_thread* PFS_thread::get_current_thre
PFS_thread* create_thread(PFS_thread_class *klass, const void *identity,
ulong thread_id)
{
- PFS_scan scan;
+ static uint thread_monotonic_index= 0;
uint index;
- uint random= randomized_index(identity, thread_max);
+ uint attempts= 0;
+ PFS_thread *pfs;
+
+ while (++attempts <= thread_max)
+ {
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& thread_monotonic_index, 1);
+ index= thread_monotonic_index % thread_max;
+ pfs= thread_array + index;
- for (scan.init(random, thread_max);
- scan.has_pass();
- scan.next_pass())
- {
- PFS_thread *pfs= thread_array + scan.first();
- PFS_thread *pfs_last= thread_array + scan.last();
- for ( ; pfs < pfs_last; pfs++)
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
- {
- pfs->m_thread_internal_id=
- PFS_atomic::add_u32(&thread_internal_id_counter, 1);
- pfs->m_parent_thread_internal_id= 0;
- pfs->m_thread_id= thread_id;
- pfs->m_event_id= 1;
- pfs->m_enabled= true;
- pfs->m_class= klass;
- pfs->m_events_waits_count= WAIT_STACK_BOTTOM;
- pfs->m_waits_history_full= false;
- pfs->m_waits_history_index= 0;
- pfs->m_stages_history_full= false;
- pfs->m_stages_history_index= 0;
- pfs->m_statements_history_full= false;
- pfs->m_statements_history_index= 0;
-
- pfs->reset_stats();
-
- pfs->m_filename_hash_pins= NULL;
- pfs->m_table_share_hash_pins= NULL;
- pfs->m_setup_actor_hash_pins= NULL;
- pfs->m_setup_object_hash_pins= NULL;
- pfs->m_user_hash_pins= NULL;
- pfs->m_account_hash_pins= NULL;
- pfs->m_host_hash_pins= NULL;
-
- pfs->m_username_length= 0;
- pfs->m_hostname_length= 0;
- pfs->m_dbname_length= 0;
- pfs->m_command= 0;
- pfs->m_start_time= 0;
- pfs->m_processlist_state_length= 0;
- pfs->m_processlist_info_length= 0;
-
- pfs->m_host= NULL;
- pfs->m_user= NULL;
- pfs->m_account= NULL;
- set_thread_account(pfs);
+ pfs->m_thread_internal_id=
+ PFS_atomic::add_u32(&thread_internal_id_counter, 1);
+ pfs->m_parent_thread_internal_id= 0;
+ pfs->m_thread_id= thread_id;
+ pfs->m_event_id= 1;
+ pfs->m_enabled= true;
+ pfs->m_class= klass;
+ pfs->m_events_waits_count= WAIT_STACK_BOTTOM;
+ pfs->m_waits_history_full= false;
+ pfs->m_waits_history_index= 0;
+ pfs->m_stages_history_full= false;
+ pfs->m_stages_history_index= 0;
+ pfs->m_statements_history_full= false;
+ pfs->m_statements_history_index= 0;
+
+ pfs->reset_stats();
+
+ pfs->m_filename_hash_pins= NULL;
+ pfs->m_table_share_hash_pins= NULL;
+ pfs->m_setup_actor_hash_pins= NULL;
+ pfs->m_setup_object_hash_pins= NULL;
+ pfs->m_user_hash_pins= NULL;
+ pfs->m_account_hash_pins= NULL;
+ pfs->m_host_hash_pins= NULL;
+
+ pfs->m_username_length= 0;
+ pfs->m_hostname_length= 0;
+ pfs->m_dbname_length= 0;
+ pfs->m_command= 0;
+ pfs->m_start_time= 0;
+ pfs->m_processlist_state_length= 0;
+ pfs->m_processlist_info_length= 0;
+
+ pfs->m_host= NULL;
+ pfs->m_user= NULL;
+ pfs->m_account= NULL;
+ set_thread_account(pfs);
- PFS_events_waits *child_wait;
- for (index= 0; index < WAIT_STACK_SIZE; index++)
- {
- child_wait= & pfs->m_events_waits_stack[index];
- child_wait->m_thread_internal_id= pfs->m_thread_internal_id;
- child_wait->m_event_id= 0;
- child_wait->m_event_type= EVENT_TYPE_STATEMENT;
- child_wait->m_wait_class= NO_WAIT_CLASS;
- }
-
- PFS_events_stages *child_stage= & pfs->m_stage_current;
- child_stage->m_thread_internal_id= pfs->m_thread_internal_id;
- child_stage->m_event_id= 0;
- child_stage->m_event_type= EVENT_TYPE_STATEMENT;
- child_stage->m_class= NULL;
- child_stage->m_timer_start= 0;
- child_stage->m_timer_end= 0;
- child_stage->m_source_file= NULL;
- child_stage->m_source_line= 0;
+ PFS_events_waits *child_wait;
+ for (index= 0; index < WAIT_STACK_SIZE; index++)
+ {
+ child_wait= & pfs->m_events_waits_stack[index];
+ child_wait->m_thread_internal_id= pfs->m_thread_internal_id;
+ child_wait->m_event_id= 0;
+ child_wait->m_event_type= EVENT_TYPE_STATEMENT;
+ child_wait->m_wait_class= NO_WAIT_CLASS;
+ }
- PFS_events_statements *child_statement;
- for (index= 0; index < statement_stack_max; index++)
- {
- child_statement= & pfs->m_statement_stack[index];
- child_statement->m_thread_internal_id= pfs->m_thread_internal_id;
- child_statement->m_event_id= 0;
- child_statement->m_event_type= EVENT_TYPE_STATEMENT;
- child_statement->m_class= NULL;
- child_statement->m_timer_start= 0;
- child_statement->m_timer_end= 0;
- child_statement->m_lock_time= 0;
- child_statement->m_source_file= NULL;
- child_statement->m_source_line= 0;
- child_statement->m_current_schema_name_length= 0;
- child_statement->m_sqltext_length= 0;
-
- child_statement->m_message_text[0]= '\0';
- child_statement->m_sql_errno= 0;
- child_statement->m_sqlstate[0]= '\0';
- child_statement->m_error_count= 0;
- child_statement->m_warning_count= 0;
- child_statement->m_rows_affected= 0;
-
- child_statement->m_rows_sent= 0;
- child_statement->m_rows_examined= 0;
- child_statement->m_created_tmp_disk_tables= 0;
- child_statement->m_created_tmp_tables= 0;
- child_statement->m_select_full_join= 0;
- child_statement->m_select_full_range_join= 0;
- child_statement->m_select_range= 0;
- child_statement->m_select_range_check= 0;
- child_statement->m_select_scan= 0;
- child_statement->m_sort_merge_passes= 0;
- child_statement->m_sort_range= 0;
- child_statement->m_sort_rows= 0;
- child_statement->m_sort_scan= 0;
- child_statement->m_no_index_used= 0;
- child_statement->m_no_good_index_used= 0;
- }
- pfs->m_events_statements_count= 0;
+ PFS_events_stages *child_stage= & pfs->m_stage_current;
+ child_stage->m_thread_internal_id= pfs->m_thread_internal_id;
+ child_stage->m_event_id= 0;
+ child_stage->m_event_type= EVENT_TYPE_STATEMENT;
+ child_stage->m_class= NULL;
+ child_stage->m_timer_start= 0;
+ child_stage->m_timer_end= 0;
+ child_stage->m_source_file= NULL;
+ child_stage->m_source_line= 0;
- pfs->m_lock.dirty_to_allocated();
- return pfs;
+ PFS_events_statements *child_statement;
+ for (index= 0; index < statement_stack_max; index++)
+ {
+ child_statement= & pfs->m_statement_stack[index];
+ child_statement->m_thread_internal_id= pfs->m_thread_internal_id;
+ child_statement->m_event_id= 0;
+ child_statement->m_event_type= EVENT_TYPE_STATEMENT;
+ child_statement->m_class= NULL;
+ child_statement->m_timer_start= 0;
+ child_statement->m_timer_end= 0;
+ child_statement->m_lock_time= 0;
+ child_statement->m_source_file= NULL;
+ child_statement->m_source_line= 0;
+ child_statement->m_current_schema_name_length= 0;
+ child_statement->m_sqltext_length= 0;
+
+ child_statement->m_message_text[0]= '\0';
+ child_statement->m_sql_errno= 0;
+ child_statement->m_sqlstate[0]= '\0';
+ child_statement->m_error_count= 0;
+ child_statement->m_warning_count= 0;
+ child_statement->m_rows_affected= 0;
+
+ child_statement->m_rows_sent= 0;
+ child_statement->m_rows_examined= 0;
+ child_statement->m_created_tmp_disk_tables= 0;
+ child_statement->m_created_tmp_tables= 0;
+ child_statement->m_select_full_join= 0;
+ child_statement->m_select_full_range_join= 0;
+ child_statement->m_select_range= 0;
+ child_statement->m_select_range_check= 0;
+ child_statement->m_select_scan= 0;
+ child_statement->m_sort_merge_passes= 0;
+ child_statement->m_sort_range= 0;
+ child_statement->m_sort_rows= 0;
+ child_statement->m_sort_scan= 0;
+ child_statement->m_no_index_used= 0;
+ child_statement->m_no_good_index_used= 0;
}
+ pfs->m_events_statements_count= 0;
+
+ pfs->m_lock.dirty_to_allocated();
+ return pfs;
}
}
}
@@ -1016,7 +1033,6 @@ find_or_create_file(PFS_thread *thread,
const char *filename, uint len)
{
PFS_file *pfs;
- PFS_scan scan;
LF_PINS *pins= get_filename_hash_pins(thread);
if (unlikely(pins == NULL))
@@ -1108,6 +1124,10 @@ find_or_create_file(PFS_thread *thread,
PFS_file **entry;
uint retry_count= 0;
const uint retry_max= 3;
+ static uint file_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+
search:
entry= reinterpret_cast<PFS_file**>
@@ -1123,60 +1143,55 @@ search:
lf_hash_search_unpin(pins);
- /* filename is not constant, just using it for noise on create */
- uint random= randomized_index(filename, file_max);
+ while (++attempts <= file_max)
+ {
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& file_monotonic_index, 1);
+ index= file_monotonic_index % file_max;
+ pfs= file_array + index;
- for (scan.init(random, file_max);
- scan.has_pass();
- scan.next_pass())
- {
- pfs= file_array + scan.first();
- PFS_file *pfs_last= file_array + scan.last();
- for ( ; pfs < pfs_last; pfs++)
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
+ pfs->m_class= klass;
+ pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
+ pfs->m_timed= klass->m_timed;
+ strncpy(pfs->m_filename, normalized_filename, normalized_length);
+ pfs->m_filename[normalized_length]= '\0';
+ pfs->m_filename_length= normalized_length;
+ pfs->m_wait_stat.reset();
+ pfs->m_file_stat.m_open_count= 1;
+ pfs->m_file_stat.m_io_stat.reset();
+
+ int res;
+ res= lf_hash_insert(&filename_hash, thread->m_filename_hash_pins,
+ &pfs);
+ if (likely(res == 0))
{
- pfs->m_class= klass;
- pfs->m_enabled= klass->m_enabled && flag_global_instrumentation;
- pfs->m_timed= klass->m_timed;
- strncpy(pfs->m_filename, normalized_filename, normalized_length);
- pfs->m_filename[normalized_length]= '\0';
- pfs->m_filename_length= normalized_length;
- pfs->m_wait_stat.reset();
- pfs->m_file_stat.m_open_count= 1;
- pfs->m_file_stat.m_io_stat.reset();
-
- int res;
- res= lf_hash_insert(&filename_hash, pins,
- &pfs);
- if (likely(res == 0))
- {
- pfs->m_lock.dirty_to_allocated();
- if (klass->is_singleton())
- klass->m_singleton= pfs;
- return pfs;
- }
+ pfs->m_lock.dirty_to_allocated();
+ if (klass->is_singleton())
+ klass->m_singleton= pfs;
+ return pfs;
+ }
- pfs->m_lock.dirty_to_free();
+ pfs->m_lock.dirty_to_free();
- if (res > 0)
+ if (res > 0)
+ {
+ /* Duplicate insert by another thread */
+ if (++retry_count > retry_max)
{
- /* Duplicate insert by another thread */
- if (++retry_count > retry_max)
- {
- /* Avoid infinite loops */
- file_lost++;
- return NULL;
- }
- goto search;
+ /* Avoid infinite loops */
+ file_lost++;
+ return NULL;
}
-
- /* OOM in lf_hash_insert */
- file_lost++;
- return NULL;
+ goto search;
}
+
+ /* OOM in lf_hash_insert */
+ file_lost++;
+ return NULL;
}
}
}
@@ -1238,35 +1253,35 @@ void destroy_file(PFS_thread *thread, PF
PFS_table* create_table(PFS_table_share *share, PFS_thread *opening_thread,
const void *identity)
{
- PFS_scan scan;
- uint random= randomized_index(identity, table_max);
+ static uint table_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+ PFS_table *pfs;
+
+ while (++attempts <= table_max)
+ {
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& table_monotonic_index, 1);
+ index= table_monotonic_index % table_max;
+ pfs= table_array + index;
- for (scan.init(random, table_max);
- scan.has_pass();
- scan.next_pass())
- {
- PFS_table *pfs= table_array + scan.first();
- PFS_table *pfs_last= table_array + scan.last();
- for ( ; pfs < pfs_last; pfs++)
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
- {
- pfs->m_identity= identity;
- pfs->m_share= share;
- pfs->m_io_enabled= share->m_enabled &&
- flag_global_instrumentation && global_table_io_class.m_enabled;
- pfs->m_io_timed= share->m_timed && global_table_io_class.m_timed;
- pfs->m_lock_enabled= share->m_enabled &&
- flag_global_instrumentation && global_table_lock_class.m_enabled;
- pfs->m_lock_timed= share->m_timed && global_table_lock_class.m_timed;
- share->inc_refcount();
- pfs->m_table_stat.reset();
- pfs->m_thread_owner= opening_thread;
- pfs->m_lock.dirty_to_allocated();
- return pfs;
- }
+ pfs->m_identity= identity;
+ pfs->m_share= share;
+ pfs->m_io_enabled= share->m_enabled &&
+ flag_global_instrumentation && global_table_io_class.m_enabled;
+ pfs->m_io_timed= share->m_timed && global_table_io_class.m_timed;
+ pfs->m_lock_enabled= share->m_enabled &&
+ flag_global_instrumentation && global_table_lock_class.m_enabled;
+ pfs->m_lock_timed= share->m_timed && global_table_lock_class.m_timed;
+ share->inc_refcount();
+ pfs->m_table_stat.reset();
+ pfs->m_thread_owner= opening_thread;
+ pfs->m_lock.dirty_to_allocated();
+ return pfs;
}
}
}
=== modified file 'storage/perfschema/pfs_instr_class.cc'
--- a/storage/perfschema/pfs_instr_class.cc 2011-08-10 06:26:45 +0000
+++ b/storage/perfschema/pfs_instr_class.cc 2011-08-10 07:06:02 +0000
@@ -1045,6 +1045,10 @@ PFS_table_share* find_or_create_table_sh
const uint retry_max= 3;
bool enabled= true;
bool timed= true;
+ static uint table_share_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+ PFS_table_share *pfs;
search:
entry= reinterpret_cast<PFS_table_share**>
@@ -1052,7 +1056,6 @@ search:
key.m_hash_key, key.m_key_length));
if (entry && (entry != MY_ERRPTR))
{
- PFS_table_share *pfs;
pfs= *entry;
pfs->inc_refcount() ;
if (compare_keys(pfs, share) != 0)
@@ -1082,58 +1085,53 @@ search:
*/
}
- PFS_scan scan;
- uint random= randomized_index(table_name, table_share_max);
-
- for (scan.init(random, table_share_max);
- scan.has_pass();
- scan.next_pass())
+ while (++attempts <= table_share_max)
{
- PFS_table_share *pfs= table_share_array + scan.first();
- PFS_table_share *pfs_last= table_share_array + scan.last();
- for ( ; pfs < pfs_last; pfs++)
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& table_share_monotonic_index, 1);
+ index= table_share_monotonic_index % table_share_max;
+ pfs= table_share_array + index;
+
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
+ pfs->m_key= key;
+ pfs->m_schema_name= &pfs->m_key.m_hash_key[1];
+ pfs->m_schema_name_length= schema_name_length;
+ pfs->m_table_name= &pfs->m_key.m_hash_key[schema_name_length + 2];
+ pfs->m_table_name_length= table_name_length;
+ pfs->m_enabled= enabled;
+ pfs->m_timed= timed;
+ pfs->init_refcount();
+ pfs->m_table_stat.reset();
+ set_keys(pfs, share);
+
+ int res;
+ res= lf_hash_insert(&table_share_hash, pins, &pfs);
+ if (likely(res == 0))
{
- pfs->m_key= key;
- pfs->m_schema_name= &pfs->m_key.m_hash_key[1];
- pfs->m_schema_name_length= schema_name_length;
- pfs->m_table_name= &pfs->m_key.m_hash_key[schema_name_length + 2];
- pfs->m_table_name_length= table_name_length;
- pfs->m_enabled= enabled;
- pfs->m_timed= timed;
- pfs->init_refcount();
- pfs->m_table_stat.reset();
- set_keys(pfs, share);
-
- int res;
- res= lf_hash_insert(&table_share_hash, pins, &pfs);
- if (likely(res == 0))
- {
- pfs->m_lock.dirty_to_allocated();
- return pfs;
- }
+ pfs->m_lock.dirty_to_allocated();
+ return pfs;
+ }
- pfs->m_lock.dirty_to_free();
+ pfs->m_lock.dirty_to_free();
- if (res > 0)
+ if (res > 0)
+ {
+ /* Duplicate insert by another thread */
+ if (++retry_count > retry_max)
{
- /* Duplicate insert by another thread */
- if (++retry_count > retry_max)
- {
- /* Avoid infinite loops */
- table_share_lost++;
- return NULL;
- }
- goto search;
+ /* Avoid infinite loops */
+ table_share_lost++;
+ return NULL;
}
-
- /* OOM in lf_hash_insert */
- table_share_lost++;
- return NULL;
+ goto search;
}
+
+ /* OOM in lf_hash_insert */
+ table_share_lost++;
+ return NULL;
}
}
}
=== modified file 'storage/perfschema/pfs_setup_actor.cc'
--- a/storage/perfschema/pfs_setup_actor.cc 2011-08-10 06:26:45 +0000
+++ b/storage/perfschema/pfs_setup_actor.cc 2011-08-10 07:06:02 +0000
@@ -165,48 +165,45 @@ int insert_setup_actor(const String *use
if (unlikely(pins == NULL))
return HA_ERR_OUT_OF_MEM;
- /* user is not constant, just using it for noise on insert */
- uint i= randomized_index(user, setup_actor_max);
+ static uint setup_actor_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+ PFS_setup_actor *pfs;
- /*
- Pass 1: [random, setup_actor_max - 1]
- Pass 2: [0, setup_actor_max - 1]
- */
- int pass;
- for (pass= 1; pass <= 2; i=0, pass++)
+ while (++attempts <= setup_actor_max)
{
- PFS_setup_actor *pfs= setup_actor_array + i;
- PFS_setup_actor *pfs_last= setup_actor_array + setup_actor_max;
- for ( ; pfs < pfs_last; pfs++)
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& setup_actor_monotonic_index, 1);
+ index= setup_actor_monotonic_index % setup_actor_max;
+ pfs= setup_actor_array + index;
+
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
- {
- set_setup_actor_key(&pfs->m_key,
- user->ptr(), user->length(),
- host->ptr(), host->length(),
- role->ptr(), role->length());
- pfs->m_username= &pfs->m_key.m_hash_key[0];
- pfs->m_username_length= user->length();
- pfs->m_hostname= pfs->m_username + pfs->m_username_length + 1;
- pfs->m_hostname_length= host->length();
- pfs->m_rolename= pfs->m_hostname + pfs->m_hostname_length + 1;
- pfs->m_rolename_length= role->length();
-
- int res;
- res= lf_hash_insert(&setup_actor_hash, pins, &pfs);
- if (likely(res == 0))
- {
- pfs->m_lock.dirty_to_allocated();
- return 0;
- }
+ set_setup_actor_key(&pfs->m_key,
+ user->ptr(), user->length(),
+ host->ptr(), host->length(),
+ role->ptr(), role->length());
+ pfs->m_username= &pfs->m_key.m_hash_key[0];
+ pfs->m_username_length= user->length();
+ pfs->m_hostname= pfs->m_username + pfs->m_username_length + 1;
+ pfs->m_hostname_length= host->length();
+ pfs->m_rolename= pfs->m_hostname + pfs->m_hostname_length + 1;
+ pfs->m_rolename_length= role->length();
- pfs->m_lock.dirty_to_free();
- if (res > 0)
- return HA_ERR_FOUND_DUPP_KEY;
- return HA_ERR_OUT_OF_MEM;
+ int res;
+ res= lf_hash_insert(&setup_actor_hash, pins, &pfs);
+ if (likely(res == 0))
+ {
+ pfs->m_lock.dirty_to_allocated();
+ return 0;
}
+
+ pfs->m_lock.dirty_to_free();
+ if (res > 0)
+ return HA_ERR_FOUND_DUPP_KEY;
+ return HA_ERR_OUT_OF_MEM;
}
}
}
=== modified file 'storage/perfschema/pfs_setup_object.cc'
--- a/storage/perfschema/pfs_setup_object.cc 2011-08-10 06:26:45 +0000
+++ b/storage/perfschema/pfs_setup_object.cc 2011-08-10 07:06:02 +0000
@@ -159,47 +159,46 @@ int insert_setup_object(enum_object_type
if (unlikely(pins == NULL))
return HA_ERR_OUT_OF_MEM;
- PFS_scan scan;
- uint random= randomized_index(object, setup_object_max);
+ static uint setup_object_monotonic_index= 0;
+ uint index;
+ uint attempts= 0;
+ PFS_setup_object *pfs;
- for (scan.init(random, setup_object_max);
- scan.has_pass();
- scan.next_pass())
+ while (++attempts <= setup_object_max)
{
- PFS_setup_object *pfs= setup_object_array + scan.first();
- PFS_setup_object *pfs_last= setup_object_array + scan.last();
+ /* See create_mutex() */
+ PFS_atomic::add_u32(& setup_object_monotonic_index, 1);
+ index= setup_object_monotonic_index % setup_object_max;
+ pfs= setup_object_array + index;
- for ( ; pfs < pfs_last; pfs++)
+ if (pfs->m_lock.is_free())
{
- if (pfs->m_lock.is_free())
+ if (pfs->m_lock.free_to_dirty())
{
- if (pfs->m_lock.free_to_dirty())
- {
- set_setup_object_key(&pfs->m_key, object_type,
- schema->ptr(), schema->length(),
- object->ptr(), object->length());
- pfs->m_schema_name= &pfs->m_key.m_hash_key[1];
- pfs->m_schema_name_length= schema->length();
- pfs->m_object_name= pfs->m_schema_name + pfs->m_schema_name_length + 1;
- pfs->m_object_name_length= object->length();
- pfs->m_enabled= enabled;
- pfs->m_timed= timed;
-
- int res;
- res= lf_hash_insert(&setup_object_hash, pins, &pfs);
- if (likely(res == 0))
- {
- pfs->m_lock.dirty_to_allocated();
- setup_objects_version++;
- return 0;
- }
+ set_setup_object_key(&pfs->m_key, object_type,
+ schema->ptr(), schema->length(),
+ object->ptr(), object->length());
+ pfs->m_schema_name= &pfs->m_key.m_hash_key[1];
+ pfs->m_schema_name_length= schema->length();
+ pfs->m_object_name= pfs->m_schema_name + pfs->m_schema_name_length + 1;
+ pfs->m_object_name_length= object->length();
+ pfs->m_enabled= enabled;
+ pfs->m_timed= timed;
- pfs->m_lock.dirty_to_free();
- if (res > 0)
- return HA_ERR_FOUND_DUPP_KEY;
- /* OOM in lf_hash_insert */
- return HA_ERR_OUT_OF_MEM;
+ int res;
+ res= lf_hash_insert(&setup_object_hash, pins, &pfs);
+ if (likely(res == 0))
+ {
+ pfs->m_lock.dirty_to_allocated();
+ setup_objects_version++;
+ return 0;
}
+
+ pfs->m_lock.dirty_to_free();
+ if (res > 0)
+ return HA_ERR_FOUND_DUPP_KEY;
+ /* OOM in lf_hash_insert */
+ return HA_ERR_OUT_OF_MEM;
}
}
}
=== modified file 'support-files/mysql.spec.sh'
--- a/support-files/mysql.spec.sh 2011-07-22 08:37:51 +0000
+++ b/support-files/mysql.spec.sh 2011-08-08 09:42:09 +0000
@@ -127,13 +127,13 @@
%define distro_description Oracle Enterprise Linux 4
%define distro_releasetag oel4
%define distro_buildreq gcc-c++ gperf ncurses-devel perl readline-devel time zlib-devel
- %define distro_requires chkconfig coreutils grep procps shadow-utils
+ %define distro_requires chkconfig coreutils grep procps shadow-utils net-tools
%else
%if "%oelver" == "5"
%define distro_description Oracle Enterprise Linux 5
%define distro_releasetag oel5
%define distro_buildreq gcc-c++ gperf ncurses-devel perl readline-devel time zlib-devel
- %define distro_requires chkconfig coreutils grep procps shadow-utils
+ %define distro_requires chkconfig coreutils grep procps shadow-utils net-tools
%else
%{error:Oracle Enterprise Linux %{oelver} is unsupported}
%endif
@@ -145,13 +145,13 @@
%define distro_description Red Hat Enterprise Linux 4
%define distro_releasetag rhel4
%define distro_buildreq gcc-c++ gperf ncurses-devel perl readline-devel time zlib-devel
- %define distro_requires chkconfig coreutils grep procps shadow-utils
+ %define distro_requires chkconfig coreutils grep procps shadow-utils net-tools
%else
%if "%rhelver" == "5"
%define distro_description Red Hat Enterprise Linux 5
%define distro_releasetag rhel5
%define distro_buildreq gcc-c++ gperf ncurses-devel perl readline-devel time zlib-devel
- %define distro_requires chkconfig coreutils grep procps shadow-utils
+ %define distro_requires chkconfig coreutils grep procps shadow-utils net-tools
%else
%{error:Red Hat Enterprise Linux %{rhelver} is unsupported}
%endif
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk-wl5378 branch (marc.alff:3372 to 3373) | Marc Alff | 10 Aug |
