0

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
UNION
...
order by value desc limit 10;
14.16 sec
  • Doesn’t MySQL have built-in partitioning? – Colin 't Hart Sep 24 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 Sep 24 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 Oct 6 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 Oct 6 at 1:23
2

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 Sep 26 at 5:56
  • What is wrong with SUBPARTITIONS? – Chloe Oct 2 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 Oct 6 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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