mysql - SQL 效率 - MySQL Case/sub 选择

Question

以下代码运行时间过长。我知道是代码:

case when (select (concat(alias,' - ',firm))
        from publiccontact where replace(replace(replace(replace(businessnumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and businessnumber <> ''
            limit 1
            ) is not null then
            (select (concat(alias,' - ',firm))
        from publiccontact where replace(replace(replace(replace(businessnumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and businessnumber <> ''
            limit 1
            ) else (select (concat(alias,' - ',firm))
        from publiccontact where replace(replace(replace(replace(mobilenumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and mobilenumber <> ''
            limit 1
            )
    end as callername,

我可以让我的 case 和 sub selects 更有效率吗？我的代码可以工作，但效率肯定不高。

select  date(instime) as date,
    Pkey as ID,
    subscriber as user,
    SUBSCRIBERNAME as userName,
    case when (select (concat(alias,' - ',firm))
        from publiccontact where replace(replace(replace(replace(businessnumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and businessnumber <> ''
            limit 1
            ) is not null then
            (select (concat(alias,' - ',firm))
        from publiccontact where replace(replace(replace(replace(businessnumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and businessnumber <> ''
            limit 1
            ) else (select (concat(alias,' - ',firm))
        from publiccontact where replace(replace(replace(replace(mobilenumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and mobilenumber <> ''
            limit 1
            )
    end as callername,
    (select (concat(alias,' - ',firm))
        from publiccontact where replace(replace(replace(replace(businessnumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.destinationnumber1 and businessnumber <> ''
            limit 1
            )
    as destinationname,
    case when direction = 1 then 'incoming' else 'outgoing' end as direction,
    case when CALLDESTINATION = 1 then 'public' else 'private' end as destination,
    startdate as StartDate,
    starttime as StartTime,
    duration as DuractionSec,
    TIMETOANSWER as TimeAnswerSec,
    TAXCHARGES as Charges,
    coalesce(callerid1,callerid2,'') as CallerID,
    coalesce(destinationnumber1,destinationnumber2,'') as DestinationNumber,
    ORIGINSUBSCRIBER as UserNumber,
    completed as CallCompleted,
    coalesce(case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 2123 then 'Incoming Call Transfered External' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1132 then 'Incoming Call Transfered External' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1131 then 'Incoming Call Transfered Interal' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1132 then 'Incoming Call Transfered Interal' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1233 then 'AMC Call Answered' else null end, 
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 2122 then 'Incoming DDI call answered by GSM' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1232 then 'AMCOutgoing' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 2111 then 'OutgoingCallTransferedInternally' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 2121 then 'OutgoingCallTransferedInternally' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 2123 then 'OutgoingCallTransferedtoExternal' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1252 then 'IncomingCallPrivateNetworkCallShouldBeIgnored' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1143 then 'IncACDCallUnaws' else null end,
    case when concat(DIRECTION,CALLDESTINATION,CALLTYPE,CALLHANDLING) = 1142 then 'IncACDCallAnswered' else null end,'') as type

from taxticketitem tax;

亲切的问候

Answer 1

MySQL 5.0 IFNULL

在第一个选择不为 null 的情况下，使用 IFNULL 表达式会切断额外的选择...

ifnull (
  (select (concat(alias,' - ',firm))
  from publiccontact where replace(replace(replace(replace(businessnumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and businessnumber <> ''
  limit 1),   
  (select (concat(alias,' - ',firm))
  from publiccontact where replace(replace(replace(replace(mobilenumber,'+44 (','0'),') ',''),'+44','0'),')','') =  tax.callerid1 and mobilenumber <> ''
  limit 1)
)