title: How to avoid IN (Select...) subquery in mysql stored procedure
body: <p>I have a stored function that needs to do a <code>select ... from ... where foo IN (select ...)</code></p>
  
  <p>The problem is that, it's incredible slow compared to doing a simple <code>where = ...</code> </p>
  
  <p>Since this syntax is know to be slow, is there any possible way to store the results into a variable and then use that variable in the <code>IN ()</code> condition instead? I don't know of a way to store a query with multiple rows into a variable.</p>
  
  <p>This is the fast equality using a variable. (but wrong b/c it only does 1 value instead of multiple)</p>
  
  <pre><code>BEGIN
  
      DECLARE average DECIMAL(10,4);
      DECLARE skuAsin VARCHAR(30);
  
      SET skuAsin = (SELECT DISTINCT asin FROM inventory WHERE sku = aSku ORDER BY id DESC LIMIT 1);
  
      SET average = (
          SELECT  avg(unitsByDay) FROM (
              SELECT i.date, sum(units_ordered) as unitsByDay from inventory i
              WHERE
                  i.asin = skuAsin &amp;&amp;
                  i.marketplace_id = mid &amp;&amp;
                  i.date between d1 and d2
              GROUP BY date
          ) as vel
      );
  
      RETURN average;
  
  END;
  </code></pre>
  
  <p>This is the slow using <code>IN (select)</code></p>
  
  <pre><code>BEGIN
  
      DECLARE average DECIMAL(10,4);
  
      SET average = (
          SELECT  avg(unitsByDay) FROM (
              SELECT i.date, sum(units_ordered) as unitsByDay from inventory i
              WHERE
                  i.asin IN (SELECT DISTINCT asin FROM inventory WHERE sku = aSku) &amp;&amp;
                  i.marketplace_id = mid &amp;&amp;
                  i.date between d1 and d2
              GROUP BY date
          ) as vel
      );
  
      RETURN average;
  
  END;
  </code></pre>