title: Mysql Casting Performance Benchmark question / Data Architecture
body: <p>Currently I'm working off of a data set which is just redicolus; a flat file from multiple vendors which has no rhyme or reason; and sits at about 200 columns.. There are 15 which are common between those 200, which I have pulled out into another table.</p>
  
  <p>From the other 185 columns they are a mix of varchar's, int's datetime's and multiple string values.</p>
  
  <p>Right now I'm trying to decide how best to store these other 185 columns; as in a flat table currently it's just proving to scale super poorly.  I have two solutions setup, however I do not know which one is better.</p>
  
  <p>One is storing the meta data for each of the columns in separate tables (seen in image) <img src="https://i.stack.imgur.com/rks2y.png" alt="Image architecture"></p>
  
  <p>However it seems that using this method; it's going to be very difficult if down the road I need to do queries on items which lie within here.</p>
  
  <p>THe other method I've thought of is throwing all the columns into a table which has id, value, datatype, than when doing queries cast the value to the datatype ie:</p>
  
  <pre><code> select * from foo where cast(col_to_query) as int &lt; 5
  </code></pre>
  
  <p>however I'm not sure what the performance is like when doing things that way.</p>
  
  <p>Question:</p>
  
  <p>Which of these two methods would be better performance wise and which one would you recommend (or if there is a better option I would love to hear it). </p>
  
  <p>Thank you</p>