List:General Discussion« Previous MessageNext Message »
From:Simon J Mudd Date:October 12 2008 12:48pm
Subject:Re: Why different engines in one database?
View as plain text  
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.
Thread
Why different engines in one database?hezjing12 Oct
  • Re: Why different engines in one database?D. Dante Lorenso12 Oct
    • Problem with GROUP BYphilip14 Oct
      • Re: Problem with GROUP BYPeter Brawley14 Oct
        • Re: Problem with GROUP BYOlexandr Melnyk14 Oct
          • Re: Problem with GROUP BYphilip15 Oct
            • Re: Problem with GROUP BYMoon's Father21 Oct
  • Re: Why different engines in one database?Simon J Mudd12 Oct