데이터베이스 질문과 답변
SELECT SQL_NO_CACHE AA.AMID, COUNT(AA.AMID) InstTot, COUNT(AA.BMID) FormalTot, SUM(AA.tFormalPrice) FormalPrice, (COUNT(AA.AMID)-COUNT(AA.BMID)-SUM(FreeCount)) UnlawTot, SUM(tBuyexpectPrice) AS BuyexpectPrice, SUM(FreeCount) FROM ( SELECT A.MID AS AMID, A.ParentGroupID, B.MID AS BMID, (COUNT(B.MID)*C.AvrPrice) AS tFormalPrice, ((COUNT(A.MID)-COUNT(B.MID))*SUM(E.BuyPrice)) AS tBuyexpectPrice ,COUNT(D.GroupID) AS FreeCount FROM SWPCInstallInfoView A LEFT JOIN SWLicenseMID B ON A.MID=B.MID AND A.ParentGroupID=B.GroupID LEFT JOIN (SELECT (SUM(BuyPrice)/COUNT(GroupID)) AvrPrice, GroupID FROM SWBuyInfo GROUP BY GroupID) C ON A.ParentGroupID = C.GroupID LEFT JOIN (SELECT E.GroupID FROM SoftwareGroup E LEFT JOIN SWBuyInfo C ON C.GroupID=E.GroupID WHERE IFNULL(C.GroupID,0)=0 AND E.Type=3 GROUP BY E.GroupID ) D ON D.GroupID = A.ParentGroupID LEFT JOIN (SELECT GroupID, BuyPrice FROM SWBuyInfo AA WHERE BuyNumber=(SELECT BuyNumber FROM SWBuyInfo WHERE GroupID=AA.GroupID ORDER BY BuyDate DESC LIMIT 1 ) ) AS E ON A.ParentGroupID=E.GroupID GROUP BY A.MID, A.ParentGroupID, B.MID, C.AvrPrice, E.BuyPrice ) AS AA GROUP BY AA.AMID ORDER BY AA.AMID;
EXPLAIN 결과
|
id
|
select_type
|
table
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
|
1
|
PRIMARY
|
|
ALL
|
|
|
|
|
2756125258
|
100
|
Using temporary; Using filesort
|
|
2
|
DERIVED
|
|
ALL
|
|
|
|
|
2665942
|
100
|
Using temporary; Using filesort
|
|
2
|
DERIVED
|
B
|
eq_ref
|
PRIMARY
|
PRIMARY
|
8
|
A.MID,A.ParentGroupID
|
1
|
100
|
Using index
|
|
2
|
DERIVED
|
|
ref
|
|
|
4
|
A.ParentGroupID
|
10
|
100
|
|
|
2
|
DERIVED
|
|
ref
|
|
|
4
|
A.ParentGroupID
|
10
|
100
|
Using index
|
|
2
|
DERIVED
|
|
ref
|
|
|
4
|
A.ParentGroupID
|
10
|
100
|
|
|
7
|
DERIVED
|
softwareinfo
|
index
|
IDX_SoftwareInfo_AppID
|
IDX_SoftwareInfo_AppID
|
4
|
|
2665942
|
100
|
Using index; Using temporary; Using filesort
|
|
7
|
DERIVED
|
softwareid
|
eq_ref
|
PRIMARY,IDX_SoftwareID_AppID_ParentGroupID
|
PRIMARY
|
4
|
softwareinfo.AppID
|
1
|
100
|
|
|
5
|
DERIVED
|
AA
|
ALL
|
|
|
|
|
488
|
100
|
Using where
|
|
6
|
DEPENDENT SUBQUERY
|
SWBuyInfo
|
ref
|
PRIMARY,IDX_SWBuyInfo_GroupID
|
PRIMARY
|
4
|
AA.GroupID
|
1
|
100
|
Using where; Using filesort
|
|
4
|
DERIVED
|
E
|
index
|
PRIMARY,IDX_SoftwareGroup_ParentID
|
PRIMARY
|
4
|
|
19074
|
100
|
Using where
|
|
4
|
DERIVED
|
C
|
ref
|
PRIMARY,IDX_SWBuyInfo_GroupID
|
PRIMARY
|
4
|
E.GroupID
|
1
|
100
|
Using where; Using index
|
|
3
|
DERIVED
|
SWBuyInfo
|
index
|
PRIMARY,IDX_SWBuyInfo_GroupID
|
PRIMARY
|
426
|
|
488
|
100
|
|
by 마농
[2014.06.03 18:46:39]
각 테이블의 PK 를 알려주세요.
by 김성진
[2014.06.09 16:13:35]
SWPCInstallInfoView(GroupID, ParentGroupID)
SWLicenseMID (MID, GroupID)
SWBuyInfo (GroupID, BuyNumber)
SoftwareGroup(GroupID)
늦게 달아 드렸네요. 죄송합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.