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


A quick and dirty solution.

Instead of messing around with the innodb_buffer_pool_size variable. I decided to use a transaction to execute the insert .. select from combined with a delete with a limit.
The final query had to be executed several times because there were several million rows in the big table for the month of august. So The final solution turned out something like this:
amado@6qube1 [~]# cat temp.sql 
-- temp.sql
start transaction;
insert into ranking_archive_201508 select * from ranking_dailyresults where date >= '2015-08-01' and date < '2015-09-01' LIMIT 500000;
delete from ranking_dailyresults where date >= '2015-08-01' and date < '2015-09-01' LIMIT 500000;
commit;
amado@6qube1 [~]# for a in {1..10}; do mysql thedatabase < temp.sql; done;

That’s it!