title: How to improve performance of nested SELECT statement in MySQL
body: <p>I have the following SQL statement which has a very bad performance:</p>
  
  <pre><code>SELECT  
  frmInstLastModifiedDate AS last_modified 
  , frmInstID AS proj_id 
  , frmInstIsApproved 
  , frmInstStatus AS proj_sts 
  , (CASE 
      WHEN frmInstCreator = 294  THEN 'M' 
      WHEN status = 'f'  THEN 'F' 
      WHEN (frmInstID IN (SELECT shr.frmInstID from tbl_frm_share as shr WHERE shr.shrMember = 294 AND shr.shrType = 'LIKE'  AND shr.frmID = inst.frmID)) THEN 'L' 
      WHEN (frmInstID IN (SELECT shr.frmInstID from tbl_frm_share as shr WHERE shr.shrMember = 294 AND shr.shrType = 'SHARE' AND shr.frmID = inst.frmID)) THEN 'S' 
      ELSE 'O' END) as proj_grp 
  , (SELECT lkpCode FROM tbl_frm_lookup WHERE lkpID = (SELECT ansValue FROM tbl_itm_answer WHERE frmInstID = proj_id AND itmID = (select itmID from tbl_frm_item where itmName = 'prjStatus' AND frmID = inst.frmID))) as prjStatus 
  , (SELECT lkpCode FROM tbl_frm_lookup WHERE lkpID = (SELECT ansValue FROM tbl_itm_answer WHERE frmInstID = proj_id AND itmID = (select itmID from tbl_frm_item where itmName = 'ProjectType' AND frmID = inst.frmID))) as ProjectType 
  , (SELECT itmID FROM tbl_itm_answer where itmID in (828,829,830,831) and frmInstID = proj_id AND SUBSTRING(ansValue,1,2) = 'on') as primIRWMObj 
  , (SELECT lkpCode FROM tbl_frm_lookup WHERE lkpID = (SELECT ansValue FROM tbl_itm_answer WHERE frmInstID = proj_id AND itmID = (select itmID from tbl_frm_item where itmName = 'PrjPSubReg'))) as ProjectSubReg 
  , frmInstCreator AS proj_creatorID 
  , frmInstCode AS proj_code 
      FROM tbl_frm_instance inst 
  WHERE status not like 'd'   
  HAVING (proj_sts like 'c' AND ('PROJECT PROPONENT' = 'ADMIN' or proj_creatorID = 294 )) 
      or (proj_sts like 'a') 
      or (proj_sts like 't' AND proj_creatorID = 294)  
      OR (proj_grp = 'S')  
  ORDER BY frmInstCreateDate DESC ;
  </code></pre>
  
  <p>The statement is created dynamically based on the options selected by users. I know the  nested select statements (like the following) is the problem, but I don't know how I can replace it</p>
  
  <pre><code>    (SELECT lkpCode FROM tbl_frm_lookup WHERE lkpID = (SELECT ansValue FROM tbl_itm_answer WHERE frmInstID = proj_id AND itmID = (select itmID from tbl_frm_item where itmName = 'prjStatus' AND frmID = inst.frmID))) as prjStatus 
  </code></pre>
  
  <p>any help would be much appreciated.</p>