[EMAIL PROTECTED] (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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
