Why should you convert myisam tables to innodb ?
For the perfectly simple reason that innodb tables do not get locked by concurrent selects & inserts. So if you find that your myisam table is suffering for too many locks – due to concurrent selects and inserts, it is time for you to covert the table to innodb.
The simple query which does the trick is
Alter table myisam_table_name engine = innodb;
This query is good for small tables, which get converted in a flash (I am refering to tables smaller than 1 GB). But when you try to run this query to alter bigger tables, it takes a huge amount of time. You have to wait – maybe hours if not days to get your job done.
Recently I had a 30 GB table which i wanted to convert to innodb and the alter query went on for 3 days after which i gave up and killed the query. And then went on finding ways to make this alter happen fast.
There are multiple ways to make your alter fast –
1. create a temporary table with engine = innodb, disable unique checks and insert into the table in batches. The sequence of sql statements for this are
create table new_table_name like old_table_name;
alter table new_table_name engine = innodb;
// loop till all records are ported
insert into new_table_name select * from old_table_name where key > something and key