diff options
Diffstat (limited to 'mysql-test/main/opt_trace.test')
| -rw-r--r-- | mysql-test/main/opt_trace.test | 65 |
1 files changed, 18 insertions, 47 deletions
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 6d67925f2ed..ef740f4ca25 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -84,23 +84,7 @@ drop table t1,t2,t0; --echo # group_by min max optimization --echo # CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); ---disable_query_log -INSERT INTO t1(a) VALUES (1), (2), (3), (4); -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; -INSERT INTO t1(a) SELECT a FROM t1; ---enable_query_log +insert into t1 select seq, mod(seq,4)+1 from seq_1_to_65536; analyze table t1; EXPLAIN SELECT DISTINCT a FROM t1; @@ -114,6 +98,7 @@ CREATE TABLE t1 (a INT, b INT, c int, d int, KEY(a,b,c,d)); INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (1,0,1,1), (3,2,3,3), (4,5,4,4); ANALYZE TABLE t1; EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; +set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; select * from information_schema.OPTIMIZER_TRACE; DROP TABLE t1; @@ -137,10 +122,6 @@ drop table t1; --echo # Late ORDER BY optimization --echo # -create table ten(a int); -insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table one_k(a int primary key); -insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table t1 ( pk int not null, a int, @@ -152,18 +133,16 @@ create table t1 ( KEY a_b(a,b) ); -insert into t1 -select a, a,a,a, 'filler-dataaa' from test.one_k; +insert into t1 select seq, seq,seq,seq, 'filler-dataaa' from seq_0_to_999; update t1 set a=1 where pk between 0 and 180; update t1 set b=2 where pk between 0 and 20; analyze table t1; -set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; - update t1 set b=2 where pk between 20 and 40; +set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; -drop table t1,ten,one_k; +drop table t1; --echo # --echo # TABLE ELIMINATION @@ -203,34 +182,23 @@ drop table t0, t1, t2, t3; --echo # IN subquery to sem-join is traced --echo # -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); -insert into t1 values (0,0),(1,1),(2,2); -create table t2 as select * from t1; - -create table t11(a int, b int); +insert into t1 select seq,seq from seq_0_to_3; -create table t10 (pk int, a int); -insert into t10 select a,a from t0; -create table t12 like t10; -insert into t12 select * from t10; - -analyze table t1,t10; +create table t2 (p int, a int); +insert into t2 select seq,seq from seq_1_to_10; set optimizer_trace='enabled=on'; -explain extended select * from t1 where a in (select pk from t10); +explain extended select * from t1 where a in (select p from t2); +insert into t2 select seq,seq from seq_10_to_100; +explain extended select * from t1 where a in (select p from t2); select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; -drop table t0,t1,t11,t10,t12,t2; +drop table t1,t2; --echo # --echo # Selectivities for columns and indexes. --echo # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); - create table t1 ( pk int, a int, @@ -238,7 +206,7 @@ b int, key pk(pk), key pk_a(pk,a), key pk_a_b(pk,a,b)); -insert into t1 select a,a,a from t0; +insert into t1 select seq,seq,seq from seq_0_to_9; ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES (); set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; @@ -250,7 +218,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1; select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables= @save_use_stat_tables; -drop table t0,t1; +drop table t1; set optimizer_trace="enabled=off"; --echo # @@ -902,7 +870,6 @@ set @save_histogram_size= @@histogram_size; set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; set optimizer_switch='rowid_filter=on'; set use_stat_tables='preferably'; -set optimizer_use_condition_selectivity=4; set histogram_size=127; create table t1 (a int, b int, c int, key(a),key(b)); insert into t1 select seq, seq*2, seq/10 from seq_1_to_1000; @@ -917,8 +884,12 @@ create table t1 (a int, b int, c int, key(a),key(b)); insert into t1 select mod(seq,10), seq, seq from seq_1_to_10000; analyze table t1; +set optimizer_use_condition_selectivity=2; +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; +set optimizer_use_condition_selectivity=4; --optimizer_trace explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; + drop table three, t1; set @@optimizer_switch= @save_optimizer_switch; |