hezjing@stripped (hezjing) writes:
> When and why we create tables in different storage engines within a same
> database?
>
> Take for example a normal inventory application that provides CRUD operation
> to
> - 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
> transactional.
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[1] comes to mind) and
therefore it can sometimes be better to use a different storage
engine.
As long as you are aware of the advantages and limitations of the
different engines you should be fine.
Simon
[1] If your sales table was huge it might make sense to have a sales table by month:
sales_200810
sales_200809
sales_200808
...
(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.
