My co-worker wants to split a large 158M row stats table into stats_jan, stats_feb, ... and use UNION to select from them for reports. Is that standard practice and is it faster than to just use the large table in place and delete rows older than one year? The table is many small rows.

mysql> describe stats;
| Field          | Type                | Null | Key | Default | Extra          |
| id             | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| badge_id       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| hit_date       | datetime            | YES  | MUL | NULL    |                |
| hit_type       | tinyint(4)          | YES  |     | NULL    |                |
| source_id      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| fingerprint_id | bigint(20) unsigned | YES  |     | NULL    |                |

I did manually split the table up and copy the rows into the appropriate month tables and created a giant UNION query. The large UNION query took 14s versus 4.5m for the single table query. Why would many smaller tables take a significantly shorter time than one large table, when it's the same number of rows total?

create table stats_jan (...);
create table stats_feb (...);
create index stats_jan_hit_date_idx on stats_jan (hit_date);
insert into stats_jan select * from stats where hit_date >= '2019-01-01' and hit_date < '2019-02-01';
delete from stats where hit_date < '2018-09-01';

The monthly tables have from 1.7M rows to 35M rows.

select host as `key`, count(*) as value from stats join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id order by value desc limit 10;
4 min 30.39 sec

flush tables;
reset query cache;

select host as `key`, count(*) as value from stats_jan join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id
order by value desc limit 10;
14.16 sec
  • Doesn’t MySQL have built-in partitioning? Commented Sep 24, 2019 at 5:13
  • 1
    Take a look here for some in-depth advice about partitioning and when it's a good solution and (maybe more importantly), when it's not!
    – Vérace
    Commented Sep 24, 2019 at 7:44
  • While you are switching to PARTITION BY RANGE, shrink those ids to 4-byte INT UNSIGNED instead of 8-byte BIGINTs.
    – Rick James
    Commented Oct 6, 2019 at 1:22
  • Also, let's see SHOW CREATE TABLE and the SELECTs that need the indexes. When adding partitioning, care is needed in redoing the indexes.
    – Rick James
    Commented Oct 6, 2019 at 1:23

1 Answer 1


Do not split the table. Use Range Partitionig instead. Study MySQL 8.0 Reference Manual / Partitioning. Use MySQL 8.0 Reference Manual / ... / ALTER TABLE Partition Operations. Keep in mind that it is best to create partitions for future periods in advance (and do not forget to create LESS THAN MAXVALUE partition). Creating new partitions and moving existing data to them at the same time can be more expensive.

Do not delete data permanently. Move it into separate archive table. If you do not have enough disk space - make a backup of such an archive table, check its validity, and only if successful then delete the table. If necessary (it will - be sure!), you can recover and use this data.

  • It is indeed faster, only 0.5s for the query. But the alter table ... reorganize partition ... to shift the dates of the partitions takes 32m! Also adding a column takes 22m. What can be done about the data definition statements to alter schema as future code versions will need to alter the schema and manager wants very low downtime during deployments?
    – Chloe
    Commented Sep 26, 2019 at 5:56
  • What is wrong with SUBPARTITIONS?
    – Chloe
    Commented Oct 2, 2019 at 16:57
  • @Chloe - I have not found anything "right" with SUBPARTITION. Meanwhile, here is more discussion of using PARTITION BY RANGE(...) for a sliding time-series: mysql.rjweb.org/doc.php/partitionmaint . Do not "shift the dates in the partitions, DROP and old one and REORGANIZE the empty future partition into tomorrow and a new future. Virtually zero time taken.
    – Rick James
    Commented Oct 6, 2019 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.