title: Storing changes on entities: Is MySQL the proper solution?
body: <p>i want to store changes that i do on my "entity" table. This should be like a log. Currently it is implemented with this table in MySQL:</p>
  
  <pre><code>CREATE TABLE `entitychange` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `entity_id` int(10) unsigned NOT NULL,
    `entitytype` enum('STRING_1','STRING_2','SOMEBOOL','SOMEDOUBLE','SOMETIMESTAMP') NOT NULL DEFAULT 'STRING_1',
    `when` TIMESTAMP NOT NULL,
    `value` TEXT,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <ul>
  <li><code>entity_id</code> = the primary key of my <code>entity</code> table.</li>
  <li><code>entitytype</code> = the field that was changed in the <code>entity</code> table. sometimes only one field is changed, sometimes multiple. one change = one row.</li>
  <li><code>value</code> = the string representation of the "new value" of the field.</li>
  </ul>
  
  <hr>
  
  <p>Example when changing Field <code>entity.somedouble</code> from 3 to 2, i run those queries:</p>
  
  <pre><code>UPDATE entity SET somedouble = 2 WHERE entity_id = 123;
  INSERT INTO entitychange (entity_id,entitytype,value) VALUES (123,'SOMEDOUBLE',2);
  </code></pre>
  
  <hr>
  
  <p>I need to <code>select</code> the changes of a specific entity and entitytype of the last 15 days. For example: The last changes with <code>SOMEDOUBLE</code> for entity_id <code>123</code> within the last 15 days.</p>
  
  <p>Now, there are two things that i dislike:</p>
  
  <ol>
  <li>All Data is stored as <code>TEXT</code> - although most (less than 1%) isn't really text, in my case, most values are <code>DOUBLE</code>. Is this a big problem?</li>
  <li>The Table is getting really, really slow when inserting, since the table already has 200 million rows. Currently my Server load is up to 10-15 because of this.</li>
  </ol>
  
  <hr>
  
  <p>My Question: <strong>How do i address those two "bottlenecks"? I need to scale.</strong></p>
  
  <p>My approaches would be:</p>
  
  <ol>
  <li>Store it like this: <a href="http://sqlfiddle.com/#!2/df9d0">http://sqlfiddle.com/#!2/df9d0</a> (click on browse) - Store the changes in the <code>entitychange</code> table and then store the value according to its datatype in <code>entitychange_[bool|timestamp|double|string]</code></li>
  <li>Use partitioning by <code>HASH(entity_id)</code> - i thought of ~50 partitions. </li>
  <li>Should I use another database system, maybe MongoDB?</li>
  </ol>