memberMc表上建有如下索引: 1. (company_code,member_id,frame_no,engine_no)
2. (company_code,dealer_code)
3. (company_code,member_id)
4. (company_code,frame_no,engine_no)
customer表上建有如下索引: 1. (company_code,member_id)
2. (company_code,dealer_code)
member表上建有如下索引: 1. (company_code,member_id)
2. (company_code,dealer_code)
3. (company_code,id_number)
mc表上建有如下索引: 1. (company_code,frame_no,engine_no)
2. (company_code,dealer_code)
现用以下这段SQL文检索总件数时，用了25min左右.(结果集:9万左右)
SQL: select count( *)
from IC_MEMBER_MC this_
inner join IC_CUSTOMER ct3_ on
this_.COMPANY_CODE = ct3_.COMPANY_CODE
and this_.MEMBER_ID = ct3_.MEMBER_ID
inner join IC_MEMBER mb4_ on
this_.COMPANY_CODE = mb4_.COMPANY_CODE
and this_.MEMBER_ID = mb4_.MEMBER_ID
inner join IC_MC mc1_ on
mc1_.COMPANY_CODE = this_.COMPANY_CODE
and mc1_.FRAME_NO = this_.FRAME_NO
and mc1_.ENGINE_NO = this_.ENGINE_NO
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
也作了如下的测试:
1. 主表统计
select count(*)
from IC_MEMBER_MC this_
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
时间:55s
2. memberMc与customer join
select count(*)
from IC_MEMBER_MC this_
inner join IC_CUSTOMER ct3_ on
this_.COMPANY_CODE = ct3_.COMPANY_CODE
and this_.MEMBER_ID = ct3_.MEMBER_ID
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
时间:6min28s
3. memberMc与member join
select count(*)
from IC_MEMBER_MC this_
inner join IC_MEMBER mb4_ on
this_.COMPANY_CODE = mb4_.COMPANY_CODE
and this_.MEMBER_ID = mb4_.MEMBER_ID
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
时间:7min, 2表外连接的时间也是7min.
4. memberMc,customer,member
时间:15min
5. memberMc,customer,member(left join)
时间:15min
