title: MySQL InnoDB Primary Key with Many Columns
body: <p>There is a table that contains more id data than real data data.</p>
  
  <pre><code>user_id int unsigned NOT NULL,
  project_id int unsigned NOT NULL,
  folder_id int unsigned NOT NULL,
  file_id int unsigned NOT NULL,
  data TEXT NOT NULL
  </code></pre>
  
  <p>The only way to create a unique primary key for this table would be a composite of (user_id, project_id, folder_id, file_id). I have frequently seen 2 column composite primary keys, but is it ok to have 4 or even more? According to <a href="https://dev.mysql.com/doc/refman/5.6/en/column-indexes.html" rel="nofollow">MySQL</a>: "All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits.", so I know at least it is possible to do.</p>
  
  <p>Past this, there are frequent queries to this table for various combinations of these ids. For example, find all projects for user X, find all files for user X, find all files for project Y and folder Z, etc. Should there be a separate individual index key on each of the id columns, or if there is a composite primary key that already contains all the columns does this make further individual keys redundant? There will be about 10 million - 50 million rows in the table at any time.</p>
  
  <p>To summarize: is it ok to have a composite primary key with 4 (or more) id columns, and if there is a composite key does it make additional individual keys for each of those columns redundant?</p>