I have a very large table ~1TB of history data in MySQL 5.6. I want to split the data into many smaller tables per sites. My current idea is to iterate in chunks of 10'000 records and inside this loop iterate through each chunk to all sites. This has taken more than 2 days (stopped).

Is there any way of improving this query?


for min_id,max_id in range(1,max_id_in_table,chunk_size):
    for site_id in [site_id_1,site_id_2,site_id_3]:
        insert into history_table_<site_id> (field_a,field_b,field_c)
        select field_a,field_b,field_c
        from history_table h  where h.site_id = <site_id> AND h.id > <min_id> AND h.id <= <max_id> ;
  • 1
    i want to split it to many smaller tables per sites Maybe partitioning by site value is enough? – Akina Oct 30 '19 at 9:53
  • not sure i am fully understand what do you mean by partitioning – junior_software Oct 30 '19 at 9:54
  • 1
  • i am using azure Azure Database for MySQL servers and one of the bottleneck is the copy DB of Azure to snapshot so i think that even when partitioning the table entire needs to be copied so i am not sure that it will solve my issue – junior_software Oct 30 '19 at 10:09
  • what happens when again the size increases? Partition is what needed splitting will take huge time but partition does that for you(internally based on partition key). To know more refer here credit @rickjames – James Oct 31 '19 at 5:37

Your Answer

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

Browse other questions tagged or ask your own question.