hezjing@stripped (hezjing) writes:
> When and why we create tables in different storage engines within a same
> Take for example a normal inventory application that provides CRUD operation
> - USER table (e.g. create new user)
> - STOCK table (e.g. when there is new stock arrives)
> - CUSTOMER table (e.g. create new customer)
> - SALE table (e.g. when a stock is bough by a customer)
> I think it is always a best choice to use InnoDB since many applications are
For transactional stuff, yes, InnoDB is probably best.
> How would one wants to create a USER table in MyISAM engine and SALE table
> in InnoDB engine?
> Can you give some example?
MyISAM is [almost] the original table type that came with MySQL so
it's still supported. It also has a smaller footprint on the
filesystem than InnoDB. There are a few things you can do with MyISAM
which can't be done with InnoDB (merge tables comes to mind) and
therefore it can sometimes be better to use a different storage
As long as you are aware of the advantages and limitations of the
different engines you should be fine.
 If your sales table was huge it might make sense to have a sales table by month:
(all the above tables HAVE to be MyISAM tables) and use a merge table
sales_all being a combination of the above tables. Many people might
suggest using a view for this but the implementation in MySQL of merge
tables is more efficient than views which is why it's frequently used.