Optimizing select in case statement in MySQL

Viewed 34

I have the following tables:

    deals:
    PositionID - int
    Login - int,
    VolumeClosed - int,
    Time = datetime

I have the following query:

select
    d.PositionID,
    d.login,
    case
        when d.VolumeClosed = 0 then d.Time
        else ''
    end as Open_Time,
    
    case
        when d.VolumeClosed = 0 then (
        select
            `TIME`
        from
            deals
        where
            volumeclosed <> 0
            and d.PositionID = PositionID)
        else ''
    end as Close_Time
from
    deals d
    
    

Is there a way to optimize the select in the Case statement ?

The result is the following:

"PositionID","Login","Open_Time","Close_Time"
0,250052,"2022-08-01 10:32:29",
6218368,250052,"2022-08-01 10:45:11","2022-08-01 10:45:12"
6218368,250052,"",""
6218374,250052,"2022-08-01 10:45:41","2022-08-01 10:45:42"
6218374,250052,"",""

enter image description here

1 Answers

This composite index on deals may speed up the subquery some:

 INDEX(PositionID,  volumeclosed, `TIME`)
Related