diff options
Diffstat (limited to 'mysql-test/main/partition_range.result')
| -rw-r--r-- | mysql-test/main/partition_range.result | 69 |
1 files changed, 53 insertions, 16 deletions
diff --git a/mysql-test/main/partition_range.result b/mysql-test/main/partition_range.result index 2c6b735560c..91ce2fcac18 100644 --- a/mysql-test/main/partition_range.result +++ b/mysql-test/main/partition_range.result @@ -19,11 +19,11 @@ insert into t1 (a,b) select seq,seq from seq_4001_to_4100; insert into t1 (a,b) select seq,seq from seq_10001_to_10100; EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10000, 1000000, 3000) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index for group-by alter table t1 partition by hash(a) partitions 1; EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10000, 1000000, 3000) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index for group-by DROP TABLE t1; create table t1 (a DATETIME) partition by range (TO_DAYS(a)) @@ -958,6 +958,11 @@ CREATE TABLE t1 ( a INT, b INT, KEY ( a, b ) +); +CREATE TABLE t1_part ( +a INT, +b INT, +KEY ( a, b ) ) PARTITION BY HASH (a) PARTITIONS 1; CREATE TABLE t2 ( a INT, @@ -969,36 +974,68 @@ INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; +INSERT INTO t1 values(10,0),(10,1),(10,2),(100,0),(100,1); +select count(*) from t1; +count(*) +85 +select count(*) from t1 where a=10; +count(*) +4 +select count(*) from t1 where a=100; +count(*) +2 +INSERT INTO t1_part SELECT * FROM t1; INSERT INTO t2 SELECT * FROM t1; -ANALYZE TABLE t1,t2; +ANALYZE TABLE t1_part,t2; Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK +test.t1_part analyze status Engine-independent statistics collected +test.t1_part analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status Table is already up to date # plans should be identical -EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; +EXPLAIN SELECT a, MAX(b) FROM t1_part WHERE a IN (10,100) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index +1 SIMPLE t1_part range a a 5 NULL 2 Using where; Using index for group-by EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index +1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by FLUSH status; -SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; +SELECT a, MAX(b) FROM t1_part WHERE a IN (10, 100) GROUP BY a; a MAX(b) 10 10 -# Should be no more than 4 reads. -SHOW status LIKE 'handler_read_key'; +100 1 +SHOW status LIKE 'handler_read%'; Variable_name Value -Handler_read_key 2 +Handler_read_first 0 +Handler_read_key 6 +Handler_read_last 1 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 FLUSH status; SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; a MAX(b) 10 10 -# Should be no more than 4 reads. -SHOW status LIKE 'handler_read_key'; +100 1 +SHOW status LIKE 'handler_read%'; Variable_name Value -Handler_read_key 2 +Handler_read_first 0 +Handler_read_key 6 +Handler_read_last 1 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +insert into t2 select 100,seq from seq_1_to_100; +EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by +DROP TABLE t1,t2; # # MDEV-18501 Partition pruning doesn't work for historical queries # @@ -1026,7 +1063,7 @@ d select * from t1 partition (p1); d 2000-01-01 00:00:01.000000 -DROP TABLE t1, t2; +DROP TABLE t1; # # MDEV-21195 INSERT chooses wrong partition for RANGE partitioning by DECIMAL column # |