Running TRUNCATE PARTITION
works fine
DROP DATABASE IF EXISTS partest;
CREATE DATABASE partest;
USE partest
CREATE TABLE rolando (
id int not null auto_increment,
LogDate DATE,
PRIMARY KEY (id,LogDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
ALTER TABLE rolando TRUNCATE PARTITION p4;
Here is the output
mysql> DROP DATABASE IF EXISTS partest;
Query OK, 1 row affected (1.55 sec)
mysql> CREATE DATABASE partest;
Query OK, 1 row affected (0.00 sec)
mysql> USE partest
Database changed
mysql> CREATE TABLE rolando (
-> id int not null auto_increment,
-> LogDate DATE,
-> PRIMARY KEY (id,LogDate)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
-> (PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
-> PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
-> PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
-> PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
-> PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
-> PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
-> PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
-> PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
-> PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
-> PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
-> PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (2.60 sec)
mysql> ALTER TABLE rolando TRUNCATE PARTITION p4;
Query OK, 0 rows affected (0.25 sec)
mysql>
OK, fine. It works. However, doing this is logically unsafe. Why ? The partitioned table is based on DAYOFYEAR(datetime_col))
. Since each partition contains a week for multiple years, running alter table t1 truncate partition p4;
would wipe out week 4 from multiple years.
OK, let's try dropping and adding the partition
mysql> ALTER TABLE rolando DROP PARTITION p4;
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE rolando ADD PARTITION
-> (PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>
This makes sense since MAXVALUE
already exists in the definition. This prevents you from adding (appending) a partition if MAXVALUE
is present.
While you could play games with ALTER TABLE ... REORGANIZE PARTITION
, the complexity isn't worth it and you won't achieve the result of wedging in a partition.
Rather that explanining further partition machinations, just do the following
- Make sure an index exists on the date field
- Perform the
DELETE
SQL already mentioned
An alternative would be to create a table with a weekly partition then drop the exact week.
It looks like a lot of columns could be 'normalized, thereby significantly decreasing the size of the table and the cost of altering it.
Virtually all columns are optional?? (I see that most are NULL
instead of NOT NULL
.) This implies that the table probably could/should be split up into multiple tables, making the ALTER
less disk-intensive.
Did you try the ALTER
with ALGORITHM=INPLACE
? If so, did it complain that AFTER
prohibits such?
If it has to rebuild the table, then it also rebuilds all the indexes. This is another tmp-space hog. So, do you really need all the tables?
That leads to another possible technique...
- Drop the secondary indexes.
- Do the non-INPLACE ALTER.
- Add the indexes back using
INPLACE
.
Best Answer
You have not said what value
innodb_file_per_table
was set to when you created the table. IfOFF
, you have no chance of shrinking any file on disk. Instead, you can only send it off to be 'free' and potentially reused by inserts. WithON
...A third option is
ALTER TABLE REORGANIZE PARTITION ...
where you essentially state the same partition as the 'from' and 'to'. This degenerate version effectively does whatOPTIMIZE PARTITION
should do (but fails to).Fourth option, which would work in some partition types:
DROP PARTITION
and add it back.Yes,
OPTIMIZE PARTITION
rebuilds the entire table. After 5.6.17 (5.7.4), you may may be able to do it non-blocking (ALGORITHM=INPLACE
).Even if
TRUNCATE
does not free the space, will you be adding data back in? Of so, then one could argue that it is "not a problem".