title: MySQL: Create a Foreign key without an Index
body: <p>Is it possible to have a foreign key without an index in MySQL 5.6.34? I want that because I created a nullable column in <strong>20M</strong> rows with a foreign key to another table. As this is a new feature, only the new rows MAY have this column filled with an actual value, and as you may expect, <strong>the cardinality of that index becomes horrible</strong>. So, for most of the time, using that index is actually a bad idea. The problem: I have tons of queries that shares this same restriction:</p>
  
  <pre><code>[...] from large_table where tenant_id = ? and nullable_foreign_key_with_index is null and [...]
  </code></pre>
  
  <p>The issue? MySQL thinks that it's a good idea to use an <strong>index_merge/intersect strategy</strong> for query resolution. In this case MySQL would do 2 queries in parallel: one with <code>tenant_id</code> (which uses a valid and good index) and another one with <code>nullable_foreign_key_with_index</code> which is bad, almost a "full table scan in parallel" given that the cardinality of this index is <strong>&lt;1000</strong> in a table with <strong>>20M</strong> rows. More details about this "problem" in <a href="https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/" rel="nofollow noreferrer">here</a></p>
  
  <p>So, what are the pssible solutions? Given that MySQL "forces" a foreign key to have an index attached:</p>
  
  <ol>
  <li><p><strong>Drop the foreign key and the index.</strong> This is bad, because in the case of a bug in the app we may compromise the referential integrity.</p></li>
  <li><p><strong>FOREIGN_KEY_CHECKS=0; Drop index; FOREIGN_KEY_CHECKS=1.</strong> This is bad, because even that the foreign key still exists, MySQL doesn't validade the column anymore to check if the value actually exists. Is that a bug?</p></li>
  <li><p><strong>Use query hints in all existing queries to make sure that we are only using the old and efficient "tenant_id_index"</strong>. This is bad because I have to hunt down all existing queries and also remember to use it again when news queries are built.</p></li>
  </ol>
  
  <p>So, how can I say: <em>"MySQL, don't bother creating an index for this foreign key, but keep validating it's content in the related table, which is indexed by primary key anyway"</em>. Am I missing something? The best idea so far is to remove the foreign key and just believe that the app is working as expected, which probably it is, but this would start a classic discussion about having constraints in APP vs DATABASE. Any ideas?</p>