I have a large MySQL table (~10 Million Rows, 6.5G) Which i use for read & write. It is MyISAM, and i get a lot of locks due to MyISAM's all table lock on writes.
I decided to try and move to InnoDB which is recommended for read/write tables, and it locks only specific rows on writes.
After converting, I tested insert statements, and it turns out it takes ~15 times more (from 0.1 sec to 1.5 sec) in the InnoDB table than in the MyISAM table. Why is that?
I haven't configured anything for InnoDB yet, and plan to add partitions also, but this figure is still unexpected for me. Of course the tables are the same, same indexes etc.
Additional info according to requests:
2 indexes. primary is data_id of type Big INT, and non unique user_id of type varchar(255).
Inserts are of ~150 rows together that have the same user_id.
Size of indexes: 200 MB in MyISAM, 400MB in InnoDB