[To the title question] Yes.
Use sane partition naming.
p0 sounds like the partition with the smallest dates. Instead call it
p20220401 should contain row starting with, not ending before April 1.
But if the "future" partition is still empty, the reorg will be very fast.
That is, reorg the empty partition called
p_2022_07 (next month) and
future just before July 1. Then just before 8/1, p_2022_07 will be full and it is time to create p_2022_08.
This will leave a trail of monthly partitions. It is [usually] not practical to worry about how many there are.
I discuss that issue (and others) in Partition
SELECTs are quite happy to search one or many or even all partitions. "Partition pruning" will happen if the
WHERE clause includes a test on the column being used as the "partition key".
Your description was not clear on why you think partitioning will help.
Show us the main
SELECTs so I can help in designing the
PRIMARY KEY and any secondary
INDEXes. The tips are different than for non-partitioned tables.
Initially create the table with a strange
PRIMARY KEY; it will never need to be changed or rebuilt:
CREATE TABLE (
id BIGINT AUTO_INCREMENT,
dt ... DATETIME,
PRIMARY KEY(id, dt),
PARTITION BY RANGE (TO_DAYS(dt)) ...
The weekly (or however often)
DROP PARTITION will throw away a bunch of ids; no problem.
REORGANIZE PARTITION will build a new partition which will receive ids with higher values and dates that are newer.
(Since you don't have that PK, there is a big effort to ALTER; this would require a one-time downtime. Using
pt-online-schema-change might be a way to avoid the downtime.)