title: Mysql Query optimization, Move a CASE Exists (SELECT...) into a left join
body: <p><strong>Update</strong>
  Here is the ddl of the two tables that the CASE statement uses</p>
  
  <pre><code>CREATE TABLE product_option (
  merchant_id                 smallint unsigned NOT NULL,
  product_option_id           smallint unsigned NOT NULL AUTO_INCREMENT,
  product_option_name         varchar(255) NOT NULL DEFAULT '',
  product_id                  int unsigned NOT NULL DEFAULT 0,    /* Option may be associated with a product  */
  package_id                  int unsigned NOT NULL DEFAULT 0,    /* or all products with this package    */
  PRIMARY KEY pk_product_option (merchant_id,product_option_id),
  
  ) TYPE=MyISAM PACK_KEYS=1 COMMENT='Options';
  
  CREATE TABLE package_2_product (
  merchant_id     smallint unsigned NOT NULL,
  package_id      int unsigned NOT NULL,
  product_id      int unsigned NOT NULL,
  
  PRIMARY KEY pk_package_2_product (merchant_id,package_id,product_id)
  
  ) TYPE=MyISAM PACK_KEYS=1 COMMENT='Link product to package';
  </code></pre>
  
  <p>I have a query...</p>
  
  <pre><code>SELECT SQL_CALC_FOUND_ROWS 
    p.*,
    CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg, 
    CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
    CASE WHEN EXISTS (
      SELECT product_option_id 
        FROM product_option
       WHERE merchant_id = 116 AND product_id = p.product_id
       UNION
      SELECT product_id FROM package_2_product
       WHERE merchant_id = 116 AND product_id = p.product_id
    ) THEN 1 ELSE 0 END AS options_exist,
    i.thumbnail,i.thumbnail_width,i.thumbnail_height,
    i.title AS thumbnail_title, i.alt AS thumbnail_alt
  FROM 
    product p 
    INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                         AND p.product_id=c2p.product_id 
                                         AND c2p.category_id = 84
    LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                         AND p.product_id = i.product_id
                                         AND i.is_default = 1 
  WHERE 
    p.merchant_id = 116
    AND FIND_IN_SET('live',p.param) &gt; 0
    AND FIND_IN_SET('wholesale-only',p.param) = 0
  ORDER BY
    p.rank, p.product_name
  LIMIT 0, 50;
  </code></pre>
  
  <p>The pertinent part being the CASE WHEN EXISTS...
  is causing the query to take multiple seconds to run.  Without the sub-select it finishes in a tenth of a second.</p>
  
  <p>I'm wondering if there is a way to move this type of case, sub-select into an inner join to get the same data, and not have the sub-select run for every row that gets returned.</p>
  
  <p>Attempting the below query with a tweak to the Coalesce line and the LEFT JOIN (SELECT...) p1</p>
  
  <p>I received this error...</p>
  
  <p>Error Code: 1054
  Unknown column 'p1.product_id' in 'on clause'</p>
  
  <p>Here is the query...</p>
  
  <pre><code>SELECT SQL_CALC_FOUND_ROWS 
    p.*,
    CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg,  
    CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
    COALESCE(p1.product_option_id, p2.product_id) IS NOT NULL AS options_exist,
    i.thumbnail,i.thumbnail_width,i.thumbnail_height,
    i.title AS thumbnail_title, i.alt AS thumbnail_alt
  FROM 
    product p 
    INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                         AND p.product_id=c2p.product_id 
                                         AND c2p.category_id = 84
    LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                         AND p.product_id = i.product_id
                                         AND i.is_default = 1 
    LEFT JOIN (
     SELECT product_option_id
       FROM product_option
      WHERE merchant_id = 116
      GROUP BY product_id) p1 on p1.product_id = p.product_id
     LEFT JOIN (
      SELECT product_id
      FROM package_2_product
     WHERE merchant_id = 116
      GROUP BY product_id) p2 on p1.product_id is null and p2.product_id = p.product_id 
  WHERE 
    p.merchant_id = 116
    AND FIND_IN_SET('live',p.param) &gt; 0
    AND FIND_IN_SET('wholesale-only',p.param) = 0
  ORDER BY
    p.rank, p.product_name
  </code></pre>