title: Mixing column and row oriented databases?
body: <p>I am currently trying to improve the performance of a web application. The goal of the application is to provide <code>(real time) analytics</code>. We have a database model that is similiar to a <code>star schema</code>, few fact tables and many dimensional tables. The database is running with <code>Mysql</code> and <code>MyIsam</code> engine.<br>
  The Fact table size can easily go into the upper millions and some dimension tables can also reach the millions.<br>
  Now the point is, select queries can get awfully slow if the dimension tables get joined on the fact tables and also aggretations are done. First thing that comes in mind when hearing this is, why not precalculate the data? This is not possible because the users are allowed to use several freely customizable filters.  </p>
  
  <p>So what I need is an all-in-one system suitable for every purpose ;) Sadly it wasn't invented yet. So I came to the idea to combine 2 existing systems. Mixing a <code>row oriented</code> and a <code>column oriented</code> database (e.g. like <code>infinidb</code> or <code>infobright</code>). Keeping the mysql MyIsam solution (for fast inserts and row based queries) and add a column oriented database (for fast aggregation operations on few columns) to it and fill it periodically (nightly) via cronjob. Problem would be when the current data (it must be real time) is queried, therefore I maybe would need to get data from both databases which can complicate things.</p>
  
  <p>First tests with infinidb showed really good performance on aggregation of a few columns, so I really think this could help me speed up the application.  </p>
  
  <p>So the question is, is this a good idea? Has somebody maybe already done this? Maybe there is are better ways to do it.  </p>
  
  <p>I have no experience in column oriented databases yet and I'm also not sure how the schema of it should look like. First tests showed good performance on the same <code>star schema like</code> structure but also in a <code>big table like</code> structure.</p>
  
  <p>I hope this question fits on SO.</p>