MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

Question

We use 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?

Answer 1

Here is something to really think about

MyISAM only caches indexes, so there is always disk I/O. InnoDB caches data and indexes. If you could guarantee that every query you call against this one table can only be fetch necessary columns and all columns can be fetched from the index only (called a covering index)

Here are some nice links on Covering Indexes:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

MyISAM can do shared reads. So can InnoDB.

Please remember that MyISAM inserts, updates, and deletes perform full tables locks each time (that can be the critical path for disk I/O in making InnoDB wait its turn in certain circumstances). If the table is ready-heavy, you may want to use a dedicated MyISAM key cache.

Heavy INSERTs from LOAD DATA INFILE will lock the MyISAM table. InnoDB can tolerate it better but check the behavior of LOAD DATA INFILE to see if it locks the table regardless of Storage Engine.

The tradeoff depends upon how often you write to the MyISAM table.

PLEASE DO NOT JOIN THE MyISAM TABLE TO ANY INNODB TABLE !!! The locking is clumsy and MyISAM changes cannot rollback.