SQL optimization-CodePudding

The select tc heckRequestId, tc ustomerName, t.i dentifyType, t.i dentifyNo, t.m obilePhone1, Name, b.org, b.org, No c.e nterDate,, dc reateDate, Arthur c. heckTimes, d.o perateType
The from (select a.c heckRequestId, a.e nterDate, a.c reateDate, a.c heckTimes from dvcicheckflow where a a.o perateType='4') c
Left the join DvciCheckInfo t on Arthur c. heckRequestId=tc heckRequestId
Inner join DvciOrganization b on b.org Id=t.org Id
Left the join (select createDate, operateType checkTimes, checkRequestId from DvciCheckFlow where (operateType='03' or operateType='5')) d on, dc heckRequestId=tc heckRequestId
Where, dc heckTimes=Arthur c. heckTimes
The order by t.org Id, tc heckRequestId, tc ustomerName,, dc reateDate;

The execution plan
1 PRIMARY & lt; Derived2 & gt; ALL 666762 Using temporary; Using filesort
1 PRIMARY t eq_ref PRIMARY, idx_dvcicheckinfo_orgid PRIMARY 8 Arthur c. heckRequestId 1 Using the where
1 PRIMARY b eq_ref PRIMARY PRIMARY 4 datavessel.t.org id 1
1 PRIMARY & lt; Derived3 & gt; Ref & lt; Auto_key0 & gt; 3 ALL DERIVED e 666762 Using the where
2 DERIVED ALL 666762 Using a where clause

CodePudding user response:

The building Lord, you at least also want to format your SQL, facilitate everybody to help you see

 
The SELECT tc HECKREQUESTID, 
Tc USTOMERNAME, 
T.I DENTIFYTYPE, 
T.I DENTIFYNO, 
T.M OBILEPHONE1, 
B.ORG the NAME, 
NO, B.ORG 
C.E NTERDATE, 
, dc REATEDATE, 
Arthur c. HECKTIMES, 
D.O PERATETYPE 
The FROM (SELECT A.C HECKREQUESTID, A.E NTERDATE, A.C REATEDATE, A.C HECKTIMES 
The FROM DVCICHECKFLOW A 
WHERE A.O PERATETYPE='4') C 
LEFT the JOIN DVCICHECKINFO T 
ON Arthur c. HECKREQUESTID=tc HECKREQUESTID 
INNER JOIN DVCIORGANIZATION B 
ON B.ORG ID=T.ORG ID 
LEFT the JOIN (SELECT CREATEDATE, OPERATETYPE CHECKTIMES, CHECKREQUESTID 
The FROM DVCICHECKFLOW 
The WHERE (OPERATETYPE='03' OR OPERATETYPE='5')) D 
ON, dc HECKREQUESTID=tc HECKREQUESTID 
WHERE, dc HECKTIMES=Arthur c. HECKTIMES 
The ORDER BY T.ORG ID, tc HECKREQUESTID, tc USTOMERNAME,, dc REATEDATE;

CodePudding user response:

I'm very sorry, later will do that, thanks, upstairs brother, big, fast speed, don't know this can optimize the,,

CodePudding user response:

(OPERATETYPE='03' OR OPERATETYPE='5')

It can change (OPERATETYPE (in '03,' 05))

SQL as if using the or would be slow, at the same time, look at your SQL execution plan to be in the database for table index,

CodePudding user response:

Generally the OR can be rewritten into UNION ALL