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> ;