INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it).
We are loading changes to this table in a nightly batch job using
LOAD DATA INFILE.
MyISAM is more compact and efficient for a reference table. But this would be the ONLY MyISAM table we use.
The workload on the rest of the tables is typical web application except for 1 table which has a heavy write load and normal read load.
- Question 1 - Is it worth the tradeoff of dedicating memory to the MyISAM cache for just this one table, when I know I need a reasonably amount of memory dedicated to the InnoDB cache for the other workload usecases?
- Question 2 - When I run
LOAD DATA INFILE 'from_nightly_batch' REPLACE INTO TABLE mytable, will MyISAM lock the table for reads? Will InnoDB? e.g. can I use the reference table while I do the nightly update in either engine?