Is a table with columns like tid(int) zid(int) direction(tinyint) type(tinyint) with quite heavy read and write operations, as well as joining to other tables in myisam, better to be engined in myisam or innodb?
I was told that innodb had worse write performance than that of myisam. is that true? and innodb is also having a table lock like myisam whenever data is not extracted with primary key. Is there any techniques to be used to join tables between tables with myisam and innodb?
By default, you should always use InnoDB. It is faster; it supports transactions, foreign keys, and row-level locking; it is more tolerant of crashes -- all-around, it is considerably better than MyISAM.
There are a few situations where MyISAM tables may be required. Two common ones are:
You are using an older version of MySQL which does not support full-text or geospatial indexes, and you need to enable these features.
(Note that, if you are using full-text indexes, you are often better off using an external search engine such as Sphinx anyway -- MySQL's full-text search is not particularly good.)
You have some tables with unusual data access patterns (in particular, extremely large write-only tables, such as logs) which can sometimes perform better under MyISAM.
Unless you are certain that these conditions apply, though, you should use InnoDB. It is almost always the correct choice.