CODEWITHSUNDEEP
mysql
Fred
Fred

Reputation: 378

How to improve performance of nested SELECT statement in MySQL

I have the following SQL statement which has a very bad performance:

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 ;

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

    (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

any help would be much appreciated.

Upvotes: 0

Views: 3214

Answers (2)

Simon Dorociak
Simon Dorociak

Reputation: 33495

Don't use nested selects, its slowest and database dirty written. The best approach is to use JOIN, use JOIN always as possible.

If you want to create IS with good perfomance(it's your work, your name) so always you have to decide for most effective, fasted, the safest approach and it is JOIN.

I recommend to you use JOIN everywhere.

I recommend to you read Understanding the Query Execution Plan.

Note: You must think sometimes "like database" not "like procedure".

Upvotes: 4

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

The least i can do ::

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 tfl
inner join tbl_itm_answer tia on (tfl.lkpID= tia.ansValue)
inner join tbl_frm_item tfi on (tia.itmID= tfi.itmID)
 WHERE frmInstID = proj_id and itmName = 'prjStatus' AND frmID = inst.frmID) as prjStatus 
, frmInstCreator AS proj_creatorID 
, frmInstCode AS proj_code

Upvotes: 2

Related Questions