title: mysql - Creating rows vs. columns performance
body: <p>I built an analytics engine that pulls 50-100 rows of raw data from my database (lets call it <code>raw_table</code>), runs a bunch statistical measurements on it in PHP and then comes up with exactly 140 datapoints that I then need to store in another table (lets call it <code>results_table</code>). All of these data points are very small ints ("40","2.23","-1024" are good examples of the types of data).</p>
  
  <p>I know the maximum # of columns for mysql is quite high (4000+) but there appears to be a lot of grey area as far as when performance really starts to degrade.</p>
  
  <p>So a few questions here on best performance practices:</p>
  
  <p>1) The 140 datapoints could be, if it is better, broken up into 20 rows of 7 data points all with the same '<code>experiment_id</code>' if fewer columns is better. HOWEVER I would always need to pull ALL 20 rows (with 7 columns each, plus id, etc) so I wouldn't think this would be better performance than pulling 1 row of 140 columns. So the question: is it better to store 20 rows of 7-9 columns (that would all need to be pulled at once) or 1 row of 140-143 columns?</p>
  
  <p>2) Given my data examples ("40","2.23","-1024" are good examples of what will be stored) I'm thinking <code>smallint</code> for the structure type. Any feedback there, performance-wise or otherwise?</p>
  
  <p>3) Any other feedback on mysql performance issues or tips is welcome.</p>
  
  <p>Thanks in advance for your input.</p>