title: MySQL table data size after modifying columns
body: <p>I've got the following situation: I want to transform a poorly constructed (and populated) MySQL table into a more space efficient one. My current table has around 60 columns, all of which are <code>VARCHAR(40)</code>. Most of the values are sensor readings and therefore numeric. Currently, the size of this table is 384MB, which I compute using the following query</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT TABLE_NAME AS `Table`, 
          ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Table size (MB)`
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = '&lt;mytable&gt;' 
  ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
  </code></pre>
  <p>I then alter the table by modifying a column I know contains integer values into integer type using</p>
  <pre class="lang-sql prettyprint-override"><code> ALTER IGNORE TABLE &lt;mytable&gt; MODIFY COLUMNNAME INTEGER;
  </code></pre>
  <p>this correctly converts all values to <code>INT(11)</code> and reduced the table to 299MB. This is way more that I anticipated. Doing this for more columns generally keeps decreasing the table size, however for some, it drastically increases the size. For example, I reduced the table to 278MB, then converted a column from <code>VARCHAR(40)</code> to <code>INT(11)</code> and obtained a size of 398MB, more than what I had when starting off.</p>
  <p>Looking for an explanation, I found that MySQL stores varchar types in L + 1 bytes (L being the length of the string). <code>INT(11)</code> consists of 4 bytes. Some columns contain many 0s, so I was not surprised to see that the total size would go up slightly for some columns as the average size per entry would increase marginally. However, the effects are way more drastic than I would expect.</p>
  <p>I am curious if there is some detail about how MySQL organises the data that explains this behaviour, any ideas?</p>
  <p>I am using MariaDB (latest) locally.</p>
  <p>(note that I'm not necessarily concerned in the performance increase of queries and inserts for this question in particular)</p>