title: MySQL query: Slow response when joining table using ifnull
body: <p>I have three tables, <code>products</code>, <code>variants</code>, and <code>data</code>. </p>
  
  <p>Each product has it's own <code>productcode</code>. Products can have any number of variants including none. Variants have their own code <code>variantcode</code>. Products have a <code>productid</code> column, and each variant has a related <code>productid</code> column.</p>
  
  <p>I want a list of all the codes, but I only care about the <code>productcode</code> if a product has no variants. Otherwise I want the <code>variantcode</code>. I wrote this query to get this list:</p>
  
  <pre><code>SELECT IFNULL(variants.variantcode, products.productcode) AS code 
  FROM products 
  LEFT OUTER JOIN variants ON (products.productid = variants.productid) 
  ORDER BY code
  </code></pre>
  
  <p>This query works as I expected. </p>
  
  <p>The <code>data</code> table contains extra data for each code. I want to join this data onto this list. I tried this query:</p>
  
  <pre><code>SELECT IFNULL(variants.variantcode, products.productcode) AS code 
  FROM products 
  LEFT OUTER JOIN variants ON (products.productid = variants.productid)
  LEFT OUTER JOIN data ON (data.partno = code) 
  ORDER BY code
  </code></pre>
  
  <p>But I get an error "Unknown column 'code' in 'on clause'". I assumed this had something to do with <code>code</code> being a generated value, so I then tried this query:</p>
  
  <pre><code>SELECT IFNULL(variants.variantcode, products.productcode) AS code 
  FROM products 
  LEFT OUTER JOIN variants ON (products.productid = variants.productid) 
  LEFT OUTER JOIN data ON (data.partno = IFNULL(variants.variantcode, products.productcode)) 
  ORDER BY code
  </code></pre>
  
  <p>This query worked, but took a long time (~20 seconds vs &lt;1 second for the first query). Is the IFNULL in the ON clause the problem? How can I speed it up?</p>