How to split a large innodb table into many tables.

Working on a large 20gb innodb table that gets new records inserted daily, I decided to archive some of the old data by splitting the table into many table_archive_2015M subtables by using its `date` column and grouping the data by month.

While running the following query:

insert into ranking_archive_201508 select * from ranking_dailyresults where date >= '2015-08-01' and date < '2015-09-01' LIMIT 500000;

I got the following error from the mysql 5.1 server.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Continue reading “How to split a large innodb table into many tables.” »