title: Strange MySQL table sizes
body: <p>I implemented an inverted index in MySQL using mainly these three tables:</p>
  
  <pre><code>WORDS (word_id, word)
  INSTANCES (word_id, doc_id)
  DOCUMENT (id, text)
  </code></pre>
  
  <p>where <code>word_id</code> is <code>INT</code> and <code>doc_id</code> is <code>BIGINT</code></p>
  
  <p>Then, I tried to de-normalize the WORDS and INSTANCES tables into one:</p>
  
  <pre><code>INSTANCES_NEW (word, doc_id)
  </code></pre>
  
  <p>where word is now <code>VARCHAR(50)</code> and <code>doc_id</code> is <code>BIGINT</code></p>
  
  <p>The tables INSTANCES and INSTANCES_NEW have both, two indexes for the <code>word_id/word</code> and <code>doc_id</code>. For the same data, INSTANCES is displayed as taking 146MB while the INSTANCES_NEW is displayed as taking 122.7MB. How can an <code>INT</code> take more space than <code>VARCHAR(50)</code>? Nearly all words take at least 3 characters - so theoretically the space taken should be more than doubled.</p>