mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
1110 lines
38 KiB
Text
1110 lines
38 KiB
Text
drop table if exists t1, t2;
|
|
#
|
|
# Bug#48229: group by performance issue of partitioned table
|
|
#
|
|
CREATE TABLE t1 (a INT,b INT,KEY a (a,b));
|
|
INSERT INTO `t1` VALUES (0,580092),(3000,894076),(4000,805483),(4000,913540),(6000,611137),(8000,171602),(9000,599495),(9000,746305),(10000,272829),(10000,847519),(12000,258869),(12000,929028),(13000,288970),(15000,20971),(15000,105839),(16000,788272),(17000,76914),(18000,827274),(19000,802258),(20000,123677),(20000,587729),(22000,701449),(25000,31565),(25000,230782),(25000,442887),(25000,733139),(25000,851020);
|
|
SELECT COUNT(*) from t1 where a IN (10000, 1000000, 3000);
|
|
COUNT(*)
|
|
3
|
|
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 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 3 Using where; Using index for group-by
|
|
alter table t1 remove partitioning;
|
|
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 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 3 Using where; Using index for group-by
|
|
DROP TABLE t1;
|
|
create table t1 (a DATETIME)
|
|
partition by range (TO_DAYS(a))
|
|
subpartition by hash(to_seconds(a))
|
|
(partition p0 values less than (1));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` datetime DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (to_days(`a`))
|
|
SUBPARTITION BY HASH (to_seconds(`a`))
|
|
(PARTITION `p0` VALUES LESS THAN (1) ENGINE = MyISAM)
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by range (a)
|
|
( partition p0 values less than (NULL),
|
|
partition p1 values less than (MAXVALUE));
|
|
ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
|
|
create table t1 (a datetime not null)
|
|
partition by range (TO_SECONDS(a))
|
|
( partition p0 VALUES LESS THAN (TO_SECONDS('2007-03-08 00:00:00')),
|
|
partition p1 VALUES LESS THAN (TO_SECONDS('2007-04-01 00:00:00')));
|
|
select partition_method, partition_expression, partition_description
|
|
from information_schema.partitions where table_name = "t1";
|
|
partition_method partition_expression partition_description
|
|
RANGE to_seconds(`a`) 63340531200
|
|
RANGE to_seconds(`a`) 63342604800
|
|
INSERT INTO t1 VALUES ('2007-03-01 12:00:00'), ('2007-03-07 12:00:00');
|
|
INSERT INTO t1 VALUES ('2007-03-08 12:00:00'), ('2007-03-15 12:00:00');
|
|
explain partitions select * from t1 where a < '2007-03-08 00:00:00';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
|
|
explain partitions select * from t1 where a < '2007-03-08 00:00:01';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where
|
|
explain partitions select * from t1 where a <= '2007-03-08 00:00:00';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where
|
|
explain partitions select * from t1 where a <= '2007-03-07 23:59:59';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
|
|
explain partitions select * from t1 where a < '2007-03-07 23:59:59';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` datetime NOT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (to_seconds(`a`))
|
|
(PARTITION `p0` VALUES LESS THAN (63340531200) ENGINE = MyISAM,
|
|
PARTITION `p1` VALUES LESS THAN (63342604800) ENGINE = MyISAM)
|
|
drop table t1;
|
|
create table t1 (a date)
|
|
partition by range(to_seconds(a))
|
|
(partition p0 values less than (to_seconds('2004-01-01')),
|
|
partition p1 values less than (to_seconds('2005-01-01')));
|
|
insert into t1 values ('2003-12-30'),('2004-12-31');
|
|
select * from t1;
|
|
a
|
|
2003-12-30
|
|
2004-12-31
|
|
explain partitions select * from t1 where a <= '2003-12-31';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1
|
|
select * from t1 where a <= '2003-12-31';
|
|
a
|
|
2003-12-30
|
|
explain partitions select * from t1 where a <= '2005-01-01';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
|
|
select * from t1 where a <= '2005-01-01';
|
|
a
|
|
2003-12-30
|
|
2004-12-31
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` date DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (to_seconds(`a`))
|
|
(PARTITION `p0` VALUES LESS THAN (63240134400) ENGINE = MyISAM,
|
|
PARTITION `p1` VALUES LESS THAN (63271756800) ENGINE = MyISAM)
|
|
drop table t1;
|
|
create table t1 (a datetime)
|
|
partition by range(to_seconds(a))
|
|
(partition p0 values less than (to_seconds('2004-01-01 12:00:00')),
|
|
partition p1 values less than (to_seconds('2005-01-01 12:00:00')));
|
|
insert into t1 values ('2004-01-01 11:59:29'),('2005-01-01 11:59:59');
|
|
select * from t1;
|
|
a
|
|
2004-01-01 11:59:29
|
|
2005-01-01 11:59:59
|
|
explain partitions select * from t1 where a <= '2004-01-01 11:59.59';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1
|
|
select * from t1 where a <= '2004-01-01 11:59:59';
|
|
a
|
|
2004-01-01 11:59:29
|
|
explain partitions select * from t1 where a <= '2005-01-01';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
|
|
select * from t1 where a <= '2005-01-01';
|
|
a
|
|
2004-01-01 11:59:29
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` datetime DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (to_seconds(`a`))
|
|
(PARTITION `p0` VALUES LESS THAN (63240177600) ENGINE = MyISAM,
|
|
PARTITION `p1` VALUES LESS THAN (63271800000) ENGINE = MyISAM)
|
|
drop table t1;
|
|
create table t1 (a int, b char(20))
|
|
partition by range columns(a,b)
|
|
(partition p0 values less than (1));
|
|
ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3
|
|
create table t1 (a int, b char(20))
|
|
partition by range(a)
|
|
(partition p0 values less than (1,"b"));
|
|
ERROR HY000: Cannot have more than one value for this type of RANGE partitioning
|
|
create table t1 (a int, b char(20))
|
|
partition by range(a)
|
|
(partition p0 values less than (1,"b"));
|
|
ERROR HY000: Cannot have more than one value for this type of RANGE partitioning
|
|
create table t1 (a int, b char(20))
|
|
partition by range columns(b)
|
|
(partition p0 values less than ("b"));
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by range (a)
|
|
( partition p0 values less than (maxvalue));
|
|
alter table t1 add partition (partition p1 values less than (100000));
|
|
ERROR HY000: MAXVALUE can only be used in last partition definition
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`a`)
|
|
(PARTITION `p0` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
|
|
drop table t1;
|
|
create table t1 (a integer)
|
|
partition by range (a)
|
|
( partition p0 values less than (4),
|
|
partition p1 values less than (100));
|
|
create trigger tr1 before insert on t1
|
|
for each row begin
|
|
set @a = 1;
|
|
end|
|
|
alter table t1 drop partition p0;
|
|
drop table t1;
|
|
create table t1 (a integer)
|
|
partition by range (a)
|
|
( partition p0 values less than (4),
|
|
partition p1 values less than (100));
|
|
LOCK TABLES t1 WRITE;
|
|
alter table t1 drop partition p0;
|
|
alter table t1 reorganize partition p1 into
|
|
( partition p0 values less than (4),
|
|
partition p1 values less than (100));
|
|
alter table t1 add partition ( partition p2 values less than (200));
|
|
UNLOCK TABLES;
|
|
drop table t1;
|
|
create table t1 (a int unsigned)
|
|
partition by range (a)
|
|
(partition pnull values less than (0),
|
|
partition p0 values less than (1),
|
|
partition p1 values less than(2));
|
|
insert into t1 values (null),(0),(1);
|
|
select * from t1 where a is null;
|
|
a
|
|
NULL
|
|
select * from t1 where a >= 0;
|
|
a
|
|
0
|
|
1
|
|
select * from t1 where a < 0;
|
|
a
|
|
select * from t1 where a <= 0;
|
|
a
|
|
0
|
|
select * from t1 where a > 1;
|
|
a
|
|
explain partitions select * from t1 where a is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 pnull system NULL NULL NULL NULL 1
|
|
explain partitions select * from t1 where a >= 0;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
|
|
explain partitions select * from t1 where a < 0;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
explain partitions select * from t1 where a <= 0;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 2 Using where
|
|
explain partitions select * from t1 where a > 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
drop table t1;
|
|
create table t1 (a int unsigned, b int unsigned)
|
|
partition by range (a)
|
|
subpartition by hash (b)
|
|
subpartitions 2
|
|
(partition pnull values less than (0),
|
|
partition p0 values less than (1),
|
|
partition p1 values less than(2));
|
|
insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1);
|
|
select * from t1 where a is null;
|
|
a b
|
|
NULL 0
|
|
NULL 1
|
|
select * from t1 where a >= 0;
|
|
a b
|
|
0 0
|
|
0 1
|
|
1 0
|
|
1 1
|
|
select * from t1 where a < 0;
|
|
a b
|
|
select * from t1 where a <= 0;
|
|
a b
|
|
0 0
|
|
0 1
|
|
select * from t1 where a > 1;
|
|
a b
|
|
explain partitions select * from t1 where a is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
|
|
explain partitions select * from t1 where a >= 0;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
|
|
explain partitions select * from t1 where a < 0;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
|
|
explain partitions select * from t1 where a <= 0;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 4 Using where
|
|
explain partitions select * from t1 where a > 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (5) tablespace ts1,
|
|
partition x2 values less than (10) tablespace ts2,
|
|
partition x3 values less than maxvalue tablespace ts3);
|
|
INSERT into t1 values (1, 1, 1);
|
|
INSERT into t1 values (6, 1, 1);
|
|
INSERT into t1 values (10, 1, 1);
|
|
INSERT into t1 values (15, 1, 1);
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
10 1 1
|
|
15 1 1
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`a`)
|
|
(PARTITION `x1` VALUES LESS THAN (5) ENGINE = MyISAM,
|
|
PARTITION `x2` VALUES LESS THAN (10) ENGINE = MyISAM,
|
|
PARTITION `x3` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
|
|
ALTER TABLE t1
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (5) tablespace ts1,
|
|
partition x2 values less than (10) tablespace ts2,
|
|
partition x3 values less than maxvalue tablespace ts3);
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
10 1 1
|
|
15 1 1
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`a`)
|
|
(PARTITION `x1` VALUES LESS THAN (5) ENGINE = MyISAM,
|
|
PARTITION `x2` VALUES LESS THAN (10) ENGINE = MyISAM,
|
|
PARTITION `x3` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null)
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (5) tablespace ts1,
|
|
partition x2 values less than (10) tablespace ts2,
|
|
partition x3 values less than maxvalue tablespace ts3);
|
|
INSERT into t1 values (1, 1, 1);
|
|
INSERT into t1 values (6, 1, 1);
|
|
INSERT into t1 values (10, 1, 1);
|
|
INSERT into t1 values (15, 1, 1);
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
10 1 1
|
|
15 1 1
|
|
ALTER TABLE t1
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (5) tablespace ts1,
|
|
partition x2 values less than (10) tablespace ts2,
|
|
partition x3 values less than maxvalue tablespace ts3);
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
10 1 1
|
|
15 1 1
|
|
drop table if exists t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (5) tablespace ts1,
|
|
partition x2 values less than (10) tablespace ts2,
|
|
partition x3 values less than (15) tablespace ts3);
|
|
INSERT into t1 values (1, 1, 1);
|
|
INSERT into t1 values (6, 1, 1);
|
|
INSERT into t1 values (10, 1, 1);
|
|
INSERT into t1 values (15, 1, 1);
|
|
ERROR HY000: Table has no partition for value 15
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
10 1 1
|
|
ALTER TABLE t1
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (5) tablespace ts1,
|
|
partition x2 values less than (10) tablespace ts2,
|
|
partition x3 values less than (15) tablespace ts3);
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
6 1 1
|
|
10 1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by range (a)
|
|
(partition x1 values less than (1));
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11,
|
|
subpartition x12),
|
|
partition x2 values less than (5)
|
|
( subpartition x21,
|
|
subpartition x22)
|
|
);
|
|
SELECT * from t1;
|
|
a b c
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a` + `b`)
|
|
(PARTITION `x1` VALUES LESS THAN (1)
|
|
(SUBPARTITION `x11` ENGINE = MyISAM,
|
|
SUBPARTITION `x12` ENGINE = MyISAM),
|
|
PARTITION `x2` VALUES LESS THAN (5)
|
|
(SUBPARTITION `x21` ENGINE = MyISAM,
|
|
SUBPARTITION `x22` ENGINE = MyISAM))
|
|
ALTER TABLE t1 ADD COLUMN d int;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
`d` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a` + `b`)
|
|
(PARTITION `x1` VALUES LESS THAN (1)
|
|
(SUBPARTITION `x11` ENGINE = MyISAM,
|
|
SUBPARTITION `x12` ENGINE = MyISAM),
|
|
PARTITION `x2` VALUES LESS THAN (5)
|
|
(SUBPARTITION `x21` ENGINE = MyISAM,
|
|
SUBPARTITION `x22` ENGINE = MyISAM))
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 tablespace t1 engine myisam nodegroup 0,
|
|
subpartition x12 tablespace t2 engine myisam nodegroup 1),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 tablespace t1 engine myisam nodegroup 0,
|
|
subpartition x22 tablespace t2 engine myisam nodegroup 1)
|
|
);
|
|
SELECT * from t1;
|
|
a b c
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 tablespace t1 nodegroup 0,
|
|
subpartition x12 tablespace t2 nodegroup 1),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 tablespace t1 nodegroup 0,
|
|
subpartition x22 tablespace t2 nodegroup 1)
|
|
);
|
|
SELECT * from t1;
|
|
a b c
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 engine myisam nodegroup 0,
|
|
subpartition x12 engine myisam nodegroup 1),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 engine myisam nodegroup 0,
|
|
subpartition x22 engine myisam nodegroup 1)
|
|
);
|
|
INSERT into t1 VALUES (1,1,1);
|
|
INSERT into t1 VALUES (4,1,1);
|
|
INSERT into t1 VALUES (5,1,1);
|
|
ERROR HY000: Table has no partition for value 5
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
ALTER TABLE t1
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 engine myisam nodegroup 0,
|
|
subpartition x12 engine myisam nodegroup 1),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 engine myisam nodegroup 0,
|
|
subpartition x22 engine myisam nodegroup 1)
|
|
);
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 tablespace t1 engine myisam,
|
|
subpartition x12 tablespace t2 engine myisam),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 tablespace t1 engine myisam,
|
|
subpartition x22 tablespace t2 engine myisam)
|
|
);
|
|
INSERT into t1 VALUES (1,1,1);
|
|
INSERT into t1 VALUES (4,1,1);
|
|
INSERT into t1 VALUES (5,1,1);
|
|
ERROR HY000: Table has no partition for value 5
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
ALTER TABLE t1
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 tablespace t1 engine myisam,
|
|
subpartition x12 tablespace t2 engine myisam),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 tablespace t1 engine myisam,
|
|
subpartition x22 tablespace t2 engine myisam)
|
|
);
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 tablespace t1,
|
|
subpartition x12 tablespace t2),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 tablespace t1,
|
|
subpartition x22 tablespace t2)
|
|
);
|
|
INSERT into t1 VALUES (1,1,1);
|
|
INSERT into t1 VALUES (4,1,1);
|
|
INSERT into t1 VALUES (5,1,1);
|
|
ERROR HY000: Table has no partition for value 5
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
ALTER TABLE t1
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 tablespace t1 engine myisam,
|
|
subpartition x12 tablespace t2 engine myisam),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 tablespace t1 engine myisam,
|
|
subpartition x22 tablespace t2 engine myisam)
|
|
);
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 engine myisam,
|
|
subpartition x12 engine myisam),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 engine myisam,
|
|
subpartition x22 engine myisam)
|
|
);
|
|
INSERT into t1 VALUES (1,1,1);
|
|
INSERT into t1 VALUES (4,1,1);
|
|
INSERT into t1 VALUES (5,1,1);
|
|
ERROR HY000: Table has no partition for value 5
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
ALTER TABLE t1
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
( partition x1 values less than (1)
|
|
( subpartition x11 engine myisam,
|
|
subpartition x12 engine myisam),
|
|
partition x2 values less than (5)
|
|
( subpartition x21 engine myisam,
|
|
subpartition x22 engine myisam)
|
|
);
|
|
SELECT * from t1;
|
|
a b c
|
|
1 1 1
|
|
4 1 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (c1 int default NULL, c2 varchar(30) default NULL,
|
|
c3 date default NULL) engine=myisam
|
|
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
|
|
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
|
|
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
|
|
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
|
|
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
|
|
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
|
|
PARTITION p11 VALUES LESS THAN MAXVALUE );
|
|
INSERT INTO t1 VALUES (1, 'testing partitions', '1995-07-17'),
|
|
(3, 'testing partitions','1995-07-31'),
|
|
(5, 'testing partitions','1995-08-13'),
|
|
(7, 'testing partitions','1995-08-26'),
|
|
(9, 'testing partitions','1995-09-09'),
|
|
(0, 'testing partitions','2000-07-10'),
|
|
(2, 'testing partitions','2000-07-23'),
|
|
(4, 'testing partitions','2000-08-05'),
|
|
(6, 'testing partitions','2000-08-19'),
|
|
(8, 'testing partitions','2000-09-01');
|
|
SELECT COUNT(*) FROM t1 WHERE c3 BETWEEN '1996-12-31' AND '2000-12-31';
|
|
COUNT(*)
|
|
5
|
|
SELECT COUNT(*) FROM t1 WHERE c3 < '2000-12-31';
|
|
COUNT(*)
|
|
10
|
|
DROP TABLE t1;
|
|
create table t1 (a bigint unsigned)
|
|
partition by range (a)
|
|
(partition p0 values less than (10),
|
|
partition p1 values less than (0));
|
|
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
|
|
create table t1 (a bigint unsigned)
|
|
partition by range (a)
|
|
(partition p0 values less than (0),
|
|
partition p1 values less than (10));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`a`)
|
|
(PARTITION `p0` VALUES LESS THAN (0) ENGINE = MyISAM,
|
|
PARTITION `p1` VALUES LESS THAN (10) ENGINE = MyISAM)
|
|
drop table t1;
|
|
create table t1 (a bigint unsigned)
|
|
partition by range (a)
|
|
(partition p0 values less than (2),
|
|
partition p1 values less than (10));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`a`)
|
|
(PARTITION `p0` VALUES LESS THAN (2) ENGINE = MyISAM,
|
|
PARTITION `p1` VALUES LESS THAN (10) ENGINE = MyISAM)
|
|
insert into t1 values (0xFFFFFFFFFFFFFFFF);
|
|
ERROR HY000: Table has no partition for value 18446744073709551615
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by range (MOD(a,3))
|
|
subpartition by hash(a)
|
|
subpartitions 2
|
|
(partition p0 values less than (1),
|
|
partition p1 values less than (2),
|
|
partition p2 values less than (3),
|
|
partition p3 values less than (4));
|
|
ALTER TABLE t1 DROP PARTITION p3;
|
|
ALTER TABLE t1 DROP PARTITION p1;
|
|
ALTER TABLE t1 DROP PARTITION p2;
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by range (MOD(a,3))
|
|
subpartition by hash(a)
|
|
subpartitions 2
|
|
(partition p0 values less than (1),
|
|
partition p1 values less than (2),
|
|
partition p2 values less than (3),
|
|
partition p3 values less than (4));
|
|
ALTER TABLE t1 DROP PARTITION p0;
|
|
ALTER TABLE t1 DROP PARTITION p1;
|
|
ALTER TABLE t1 DROP PARTITION p2;
|
|
drop table t1;
|
|
create table t1 (a int DEFAULT NULL,
|
|
b varchar(30) DEFAULT NULL,
|
|
c date DEFAULT NULL)
|
|
ENGINE=MYISAM DEFAULT CHARSET=latin1;
|
|
insert into t1 values (1, 'abc', '1995-01-01');
|
|
insert into t1 values (1, 'abc', '1995-01-02');
|
|
insert into t1 values (1, 'abc', '1995-01-03');
|
|
insert into t1 values (1, 'abc', '1995-01-04');
|
|
insert into t1 values (1, 'abc', '1995-01-05');
|
|
insert into t1 values (1, 'abc', '1995-01-06');
|
|
insert into t1 values (1, 'abc', '1995-01-07');
|
|
insert into t1 values (1, 'abc', '1995-01-08');
|
|
insert into t1 values (1, 'abc', '1995-01-09');
|
|
insert into t1 values (1, 'abc', '1995-01-10');
|
|
insert into t1 values (1, 'abc', '1995-01-11');
|
|
insert into t1 values (1, 'abc', '1995-01-12');
|
|
insert into t1 values (1, 'abc', '1995-01-13');
|
|
insert into t1 values (1, 'abc', '1995-01-14');
|
|
insert into t1 values (1, 'abc', '1995-01-15');
|
|
insert into t1 values (1, 'abc', '1997-01-01');
|
|
insert into t1 values (1, 'abc', '1997-01-02');
|
|
insert into t1 values (1, 'abc', '1997-01-03');
|
|
insert into t1 values (1, 'abc', '1997-01-04');
|
|
insert into t1 values (1, 'abc', '1997-01-05');
|
|
insert into t1 values (1, 'abc', '1997-01-06');
|
|
insert into t1 values (1, 'abc', '1997-01-07');
|
|
insert into t1 values (1, 'abc', '1997-01-08');
|
|
insert into t1 values (1, 'abc', '1997-01-09');
|
|
insert into t1 values (1, 'abc', '1997-01-10');
|
|
insert into t1 values (1, 'abc', '1997-01-11');
|
|
insert into t1 values (1, 'abc', '1997-01-12');
|
|
insert into t1 values (1, 'abc', '1997-01-13');
|
|
insert into t1 values (1, 'abc', '1997-01-14');
|
|
insert into t1 values (1, 'abc', '1997-01-15');
|
|
insert into t1 values (1, 'abc', '1998-01-01');
|
|
insert into t1 values (1, 'abc', '1998-01-02');
|
|
insert into t1 values (1, 'abc', '1998-01-03');
|
|
insert into t1 values (1, 'abc', '1998-01-04');
|
|
insert into t1 values (1, 'abc', '1998-01-05');
|
|
insert into t1 values (1, 'abc', '1998-01-06');
|
|
insert into t1 values (1, 'abc', '1998-01-07');
|
|
insert into t1 values (1, 'abc', '1998-01-08');
|
|
insert into t1 values (1, 'abc', '1998-01-09');
|
|
insert into t1 values (1, 'abc', '1998-01-10');
|
|
insert into t1 values (1, 'abc', '1998-01-11');
|
|
insert into t1 values (1, 'abc', '1998-01-12');
|
|
insert into t1 values (1, 'abc', '1998-01-13');
|
|
insert into t1 values (1, 'abc', '1998-01-14');
|
|
insert into t1 values (1, 'abc', '1998-01-15');
|
|
insert into t1 values (1, 'abc', '1999-01-01');
|
|
insert into t1 values (1, 'abc', '1999-01-02');
|
|
insert into t1 values (1, 'abc', '1999-01-03');
|
|
insert into t1 values (1, 'abc', '1999-01-04');
|
|
insert into t1 values (1, 'abc', '1999-01-05');
|
|
insert into t1 values (1, 'abc', '1999-01-06');
|
|
insert into t1 values (1, 'abc', '1999-01-07');
|
|
insert into t1 values (1, 'abc', '1999-01-08');
|
|
insert into t1 values (1, 'abc', '1999-01-09');
|
|
insert into t1 values (1, 'abc', '1999-01-10');
|
|
insert into t1 values (1, 'abc', '1999-01-11');
|
|
insert into t1 values (1, 'abc', '1999-01-12');
|
|
insert into t1 values (1, 'abc', '1999-01-13');
|
|
insert into t1 values (1, 'abc', '1999-01-14');
|
|
insert into t1 values (1, 'abc', '1999-01-15');
|
|
insert into t1 values (1, 'abc', '2000-01-01');
|
|
insert into t1 values (1, 'abc', '2000-01-02');
|
|
insert into t1 values (1, 'abc', '2000-01-03');
|
|
insert into t1 values (1, 'abc', '2000-01-04');
|
|
insert into t1 values (1, 'abc', '2000-01-05');
|
|
insert into t1 values (1, 'abc', '2000-01-06');
|
|
insert into t1 values (1, 'abc', '2000-01-07');
|
|
insert into t1 values (1, 'abc', '2000-01-08');
|
|
insert into t1 values (1, 'abc', '2000-01-09');
|
|
insert into t1 values (1, 'abc', '2000-01-15');
|
|
insert into t1 values (1, 'abc', '2000-01-11');
|
|
insert into t1 values (1, 'abc', '2000-01-12');
|
|
insert into t1 values (1, 'abc', '2000-01-13');
|
|
insert into t1 values (1, 'abc', '2000-01-14');
|
|
insert into t1 values (1, 'abc', '2000-01-15');
|
|
insert into t1 values (1, 'abc', '2001-01-01');
|
|
insert into t1 values (1, 'abc', '2001-01-02');
|
|
insert into t1 values (1, 'abc', '2001-01-03');
|
|
insert into t1 values (1, 'abc', '2001-01-04');
|
|
insert into t1 values (1, 'abc', '2001-01-05');
|
|
insert into t1 values (1, 'abc', '2001-01-06');
|
|
insert into t1 values (1, 'abc', '2001-01-07');
|
|
insert into t1 values (1, 'abc', '2001-01-08');
|
|
insert into t1 values (1, 'abc', '2001-01-09');
|
|
insert into t1 values (1, 'abc', '2001-01-15');
|
|
insert into t1 values (1, 'abc', '2001-01-11');
|
|
insert into t1 values (1, 'abc', '2001-01-12');
|
|
insert into t1 values (1, 'abc', '2001-01-13');
|
|
insert into t1 values (1, 'abc', '2001-01-14');
|
|
insert into t1 values (1, 'abc', '2001-01-15');
|
|
alter table t1
|
|
partition by range (year(c))
|
|
(partition p5 values less than (2000), partition p10 values less than (2010));
|
|
alter table t1
|
|
reorganize partition p5 into
|
|
(partition p1 values less than (1996),
|
|
partition p2 values less than (1997),
|
|
partition p3 values less than (1998),
|
|
partition p4 values less than (1999),
|
|
partition p5 values less than (2000));
|
|
drop table t1;
|
|
CREATE TABLE t1 (a date)
|
|
PARTITION BY RANGE (TO_DAYS(a))
|
|
(PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01')),
|
|
PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01')),
|
|
PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01')),
|
|
PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01')),
|
|
PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01')),
|
|
PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01')),
|
|
PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01')),
|
|
PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01')),
|
|
PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01')),
|
|
PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01')),
|
|
PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01')),
|
|
PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01')),
|
|
PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01')),
|
|
PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01')),
|
|
PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01')),
|
|
PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01')),
|
|
PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01')),
|
|
PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01')),
|
|
PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01')),
|
|
PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01')),
|
|
PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01')),
|
|
PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01')),
|
|
PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01')),
|
|
PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01')),
|
|
PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01')),
|
|
PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01')),
|
|
PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01')),
|
|
PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01')),
|
|
PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01')),
|
|
PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01')),
|
|
PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01')),
|
|
PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01')));
|
|
INSERT INTO t1 VALUES ('2003-01-13'),('2003-06-20'),('2003-08-30');
|
|
INSERT INTO t1 VALUES ('2003-04-13'),('2003-07-20'),('2003-10-30');
|
|
INSERT INTO t1 VALUES ('2003-05-13'),('2003-11-20'),('2003-12-30');
|
|
INSERT INTO t1 VALUES ('2004-01-13'),('2004-01-20'),('2004-01-30');
|
|
INSERT INTO t1 VALUES ('2004-02-13'),('2004-02-20'),('2004-02-28');
|
|
INSERT INTO t1 VALUES ('2004-03-13'),('2004-03-20'),('2004-03-30');
|
|
INSERT INTO t1 VALUES ('2004-04-13'),('2004-04-20'),('2004-04-30');
|
|
INSERT INTO t1 VALUES ('2004-05-13'),('2004-05-20'),('2004-05-30');
|
|
INSERT INTO t1 VALUES ('2004-06-13'),('2004-06-20'),('2004-06-30');
|
|
INSERT INTO t1 VALUES ('2004-07-13'),('2004-07-20'),('2004-07-30');
|
|
INSERT INTO t1 VALUES ('2004-08-13'),('2004-08-20'),('2004-08-30');
|
|
INSERT INTO t1 VALUES ('2004-09-13'),('2004-09-20'),('2004-09-30');
|
|
INSERT INTO t1 VALUES ('2004-10-13'),('2004-10-20'),('2004-10-30');
|
|
INSERT INTO t1 VALUES ('2004-11-13'),('2004-11-20'),('2004-11-30');
|
|
INSERT INTO t1 VALUES ('2004-12-13'),('2004-12-20'),('2004-12-30');
|
|
INSERT INTO t1 VALUES ('2005-01-13'),('2005-01-20'),('2005-01-30');
|
|
INSERT INTO t1 VALUES ('2005-02-13'),('2005-02-20'),('2005-02-28');
|
|
INSERT INTO t1 VALUES ('2005-03-13'),('2005-03-20'),('2005-03-30');
|
|
INSERT INTO t1 VALUES ('2005-04-13'),('2005-04-20'),('2005-04-30');
|
|
INSERT INTO t1 VALUES ('2005-05-13'),('2005-05-20'),('2005-05-30');
|
|
INSERT INTO t1 VALUES ('2005-06-13'),('2005-06-20'),('2005-06-30');
|
|
INSERT INTO t1 VALUES ('2005-07-13'),('2005-07-20'),('2005-07-30');
|
|
INSERT INTO t1 VALUES ('2005-08-13'),('2005-08-20'),('2005-08-30');
|
|
INSERT INTO t1 VALUES ('2005-09-13'),('2005-09-20'),('2005-09-30');
|
|
INSERT INTO t1 VALUES ('2005-10-13'),('2005-10-20'),('2005-10-30');
|
|
INSERT INTO t1 VALUES ('2005-11-13'),('2005-11-20'),('2005-11-30');
|
|
INSERT INTO t1 VALUES ('2005-12-13'),('2005-12-20'),('2005-12-30');
|
|
INSERT INTO t1 VALUES ('2006-01-13'),('2006-01-20'),('2006-01-30');
|
|
INSERT INTO t1 VALUES ('2006-02-13'),('2006-02-20'),('2006-02-28');
|
|
INSERT INTO t1 VALUES ('2006-03-13'),('2006-03-20'),('2006-03-30');
|
|
INSERT INTO t1 VALUES ('2006-04-13'),('2006-04-20'),('2006-04-30');
|
|
INSERT INTO t1 VALUES ('2006-05-13'),('2006-05-20'),('2006-05-30');
|
|
INSERT INTO t1 VALUES ('2006-06-13'),('2006-06-20'),('2006-06-30');
|
|
INSERT INTO t1 VALUES ('2006-07-13'),('2006-07-20'),('2006-07-30');
|
|
SELECT * FROM t1
|
|
WHERE a >= '2004-07-01' AND a <= '2004-09-30';
|
|
a
|
|
2004-07-13
|
|
2004-07-20
|
|
2004-07-30
|
|
2004-08-13
|
|
2004-08-20
|
|
2004-08-30
|
|
2004-09-13
|
|
2004-09-20
|
|
2004-09-30
|
|
EXPLAIN PARTITIONS SELECT * FROM t1
|
|
WHERE a >= '2004-07-01' AND a <= '2004-09-30';
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p3xx,p407,p408,p409 ALL NULL NULL NULL NULL 18 Using where
|
|
SELECT * from t1
|
|
WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
|
|
(a >= '2005-07-01' AND a <= '2005-09-30');
|
|
a
|
|
2004-07-13
|
|
2004-07-20
|
|
2004-07-30
|
|
2004-08-13
|
|
2004-08-20
|
|
2004-08-30
|
|
2004-09-13
|
|
2004-09-20
|
|
2004-09-30
|
|
2005-07-13
|
|
2005-07-20
|
|
2005-07-30
|
|
2005-08-13
|
|
2005-08-20
|
|
2005-08-30
|
|
2005-09-13
|
|
2005-09-20
|
|
2005-09-30
|
|
EXPLAIN PARTITIONS SELECT * from t1
|
|
WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
|
|
(a >= '2005-07-01' AND a <= '2005-09-30');
|
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 p3xx,p407,p408,p409,p507,p508,p509 ALL NULL NULL NULL NULL 27 Using where
|
|
DROP TABLE t1;
|
|
create table t1 (a int);
|
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
CREATE TABLE t2 (
|
|
defid int(10) unsigned NOT NULL,
|
|
day int(10) unsigned NOT NULL,
|
|
count int(10) unsigned NOT NULL,
|
|
filler char(200),
|
|
KEY (defid,day)
|
|
)
|
|
PARTITION BY RANGE (day) (
|
|
PARTITION p7 VALUES LESS THAN (20070401) ,
|
|
PARTITION p8 VALUES LESS THAN (20070501));
|
|
insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B;
|
|
insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B;
|
|
insert into t2 values(52, 20070321, 123, 'filler') ;
|
|
insert into t2 values(52, 20070322, 456, 'filler') ;
|
|
select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid;
|
|
sum(count)
|
|
579
|
|
drop table t1, t2;
|
|
#
|
|
# Bug#50939: Loose Index Scan unduly relies on engine to remember range
|
|
# endpoints
|
|
#
|
|
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,
|
|
b INT,
|
|
KEY ( a, b )
|
|
);
|
|
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
|
|
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_part,t2;
|
|
Table Op Msg_type Msg_text
|
|
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_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_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 for group-by
|
|
FLUSH status;
|
|
SELECT a, MAX(b) FROM t1_part WHERE a IN (10, 100) GROUP BY a;
|
|
a MAX(b)
|
|
10 10
|
|
100 1
|
|
SHOW status LIKE 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 6
|
|
Handler_read_last 0
|
|
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
|
|
100 1
|
|
SHOW status LIKE 'handler_read%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 6
|
|
Handler_read_last 0
|
|
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,t1_part,t2;
|
|
#
|
|
# MDEV-18501 Partition pruning doesn't work for historical queries
|
|
#
|
|
set time_zone= '+00:00';
|
|
create or replace table t1 (d datetime(6))
|
|
partition by range (unix_timestamp(d)) (
|
|
partition p0 values less than (1),
|
|
partition p1 values less than (maxvalue));
|
|
ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
|
|
# DECIMAL functions are now allowed, partitioning is done by integer part
|
|
create or replace table t1 (d timestamp(6))
|
|
partition by range (unix_timestamp(d)) (
|
|
partition p0 values less than (946684801),
|
|
partition p1 values less than (maxvalue));
|
|
insert into t1 values
|
|
# go to p0
|
|
('2000-01-01 00:00:00'),
|
|
('2000-01-01 00:00:00.000001'),
|
|
# goes to p1
|
|
('2000-01-01 00:00:01');
|
|
select * from t1 partition (p0);
|
|
d
|
|
2000-01-01 00:00:00.000000
|
|
2000-01-01 00:00:00.000001
|
|
select * from t1 partition (p1);
|
|
d
|
|
2000-01-01 00:00:01.000000
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-21195 INSERT chooses wrong partition for RANGE partitioning by DECIMAL column
|
|
#
|
|
create or replace table t (
|
|
d decimal(2,1)) partition by range (d)
|
|
(partition p1 values less than (10));
|
|
insert into t values (9.9);
|
|
create or replace table t (
|
|
d decimal(2,1)) partition by range (d)
|
|
(partition p1 values less than (10),
|
|
partition p2 values less than (20));
|
|
insert into t values (9.9);
|
|
select * from t partition (p1);
|
|
d
|
|
9.9
|
|
select * from t partition (p2);
|
|
d
|
|
create or replace table t (
|
|
d decimal(2,1)) partition by range (d)
|
|
(partition p1 values less than (-3));
|
|
insert into t values (-3.3);
|
|
create or replace table t (
|
|
d decimal(2,1)) partition by range (d+1)
|
|
(partition p1 values less than (10),
|
|
partition p2 values less than (20));
|
|
insert into t values (8.9);
|
|
select * from t partition (p1);
|
|
d
|
|
8.9
|
|
select * from t partition (p2);
|
|
d
|
|
set time_zone='+00:00';
|
|
create or replace table t (
|
|
d timestamp(1)) partition by range (unix_timestamp(d))
|
|
(partition p1 values less than (1577836800),
|
|
partition p2 values less than (2000000000));
|
|
insert into t values (from_unixtime(1577836799.9));
|
|
select * from t partition (p1);
|
|
d
|
|
2019-12-31 23:59:59.9
|
|
select * from t partition (p2);
|
|
d
|
|
set time_zone=default;
|
|
drop table t;
|