What will be the best storage engine for table with 74 million records, currently stored as MyISAM table size 7.6G.
Operations are mostly select and update. DDL operations (ALTER ADD COLUMN) are occasionally performed.
Joins and alter operations on MyISAM tables are extremely slow.
Will InnoDB with large innodb_buffer_pool_size be optimal for this scenario or shall I go with In-memory tables?
For virtually all use cases, InnoDB out-performs MyISAM.
DDL operations have been greatly sped up in 5.6 and 5.7 for InnoDB.
InnoDB will be 15-20GB for that table. Please provide SHOW CREATE TABLE for critique of how to save space.
Please provide some of you SELECTs for critique on how to improve JOIN performance. See also Converting MyISAM to InnoDB.
After switching to InnoDB, set key_buffer_size = 20M and innodb_buffer_pool_size to 70% of RAM.
InnoDB will 'cache' data and indexes, so it is often as fast as "in memory tables".
