diff options
Diffstat (limited to 'mysql-test/main/opt_trace.test')
-rw-r--r-- | mysql-test/main/opt_trace.test | 37 |
1 files changed, 37 insertions, 0 deletions
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 944da995aba..7cdc95c12bf 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -46,3 +46,40 @@ insert into t2 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B; explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b; select * from information_schema.OPTIMIZER_TRACE; drop table t1,t2,t0; + + +--echo # +--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 +OPTIMIZE TABLE t1; +EXPLAIN SELECT DISTINCT a FROM t1; +select * from information_schema.OPTIMIZER_TRACE; +drop table t1; + +--echo # +--echo # With group by , where clause and MIN/MAX function +--echo # +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; +select * from information_schema.OPTIMIZER_TRACE; +DROP TABLE t1; |