By now I have gone through threads and 'InnoDB vs MyISAM' pages for over two hours, but still have not the slightest idea what to choose. I have been working until today only with myISAM and found that it worked quite ok for smaller sites. However, now I am building a site that will be used quite extensively, so a good dbase scheme seems essential.
Situation in MyISAM:
- 1 main table with about 10 relational tables (it is like a news site/feed).
- INSERTs do happen in batches (news content) and are not UPDATEd (or at least not frequently)
- SELECTs happen at the user side only (heavily)
- Not all tables are always being used per SELECT, so a JOIN is not always needed.
- The INSERTs should not slow down SELECTs (or at least, not noticeable)
Dilemma:
Speed, size of tables (continuity) and security!
Should I (besides the integrity) use InnoDB (for the relational tables) or link them myself when needed by JOIN? I understand from other posts that JOIN is quite slow, but considering I dont always need to JOIN tables, makes the dilemma whether to use InnoDB, that somehow always have to make the relational link and might slow down, or use myISAM to ensure the speed of SELECTs? Basically, InnoDB would of course be easier and more secure, I just have no idea how much slower this would be compared to myISAM.
Idea / Future
For the speed I have thought about making temporary tables and use MERGE to have access to them all. Does this work on InnoDB as well? And if InnoDB would be better in some occasions, would it be smart to combine MyISAM and InnoDB?
I realize these are quite some questions with not a single answer. Nevertheless I hope there is someone that can enlighten me with those questions..
