title: Is it better to have many columns, or many tables?
body: <p>Imagine a hypothetical database, which is storing products. Each product have have 100 attributes, although any given product will only have values set for ~50 of these. I can see three ways to store this data:</p>
  
  <ol>
  <li><p>A single table with 100 columns,</p></li>
  <li><p>A single table with very few (say the 10 columns that have a value for every product), and another table with columns (product_id, attribute, value). I.e, An EAV data store.</p></li>
  <li><p>A separate table for every columns. So the core products table might have 2 columns, and there would be 98 other tables, each with the two columns (product_id, value).</p></li>
  </ol>
  
  <p>Setting aside the shades of grey between these extremes, from a pure efficiency standpoint, which is best to use? I assume it depends on the types of queries being run, i.e. if most queries are for several attributes of a product, or the value of a single attribute for several products. How does this affect the efficiency?</p>
  
  <p>Assume this is a MySQL database using InnoDB, and all tables have appropriate foreign keys, and an index on the product_id. Imagine that the attribute names and values are strings, and are not indexed.</p>
  
  <p>In a general sense, I am asking whether accessing a really big table takes more or less time than a query with many joins.</p>
  
  <p>I found a similar question here: <a href="https://stackoverflow.com/questions/428243/best-to-have-hundreds-of-columns-or-split-into-multiple-tables">Best to have hundreds of columns or split into multiple tables?</a></p>
  
  <p>The difference is, that question is asking about a specific case, and doesn't really tell me about efficiency in the general case. Other similar questions are all talking about the best way to organize the data, I just want to know how the different organizational systems impact the speed of queries.</p>