现有4个表，如下所示
t_customer_info(5W数据)
t_customer_rep(15W数据)
t_customer_activity(15W数据)
t_customer_talk(5W数据)
因业务需求，编写以下SQL语句：
select count(*) from (select id,nature_id,name,province_id,city_id,town_id,first_address,first_business_id,product_name,pay_condition,credit,(case when tel_c is null then 0 else tel_c end) tel_c,(case when visit_c is null then 0 else visit_c end) visit_c,(case when topic_c is null then 0 else topic_c end) topic_c,(case when talk_c is null then 0 else talk_c end) talk_c,(case when sale_c is null then 0 else sale_c end) sale_c,(case when lend_c is null then 0 else lend_c end) lend_c from t_customer_info A left join (select customer_id,sum(tel_c) tel_c, sum(visit_c) visit_c,sum(topic_c) topic_c,sum(talk_c) talk_c,sum(sale_c) sale_c,sum(lend_c) lend_c from (select customer_id,tel_c,visit_c,topic_c,talk_c,sale_c,lend_c from t_customer_rep A left join (select rep_id,sum(case when type_id='6952' then 1 else 0 end) as tel_c,sum(case when type_id='6953' then 1 else 0 end) as visit_c from t_customer_activety where delete_flag=0 group by rep_id) B on A.id=B.rep_id left join (select rep_id,sum(case when talk_type_id=2 then 1 else 0 end) as topic_c,sum(case when talk_type_id=4 then 1 else 0 end) as talk_c,sum(case when talk_type_id=5 then 1 else 0 end) as sale_c,sum(case when talk_type_id=6 then 1 else 0 end) as lend_c from t_customer_talk where delete_flag=0 group by rep_id) C on A.id=C.rep_id where delete_flag=0) A where 1=1 group by customer_id) B on A.id=B.customer_id where 1=1 and delete_flag=0 group by id having 1=1) A
重启服务后查询速度如下
17:37:03 1 row(s) returned 14.524sec / 0.000 sec
再次查询速度如下
17:39:17 1 row(s) returned 2.043 sec / 0.000 sec
再此向各位请教以下问题：
1、为什么两都速度相差如此之大？我想确认是不是query_cach的引起的，但当我将query_cach_type设为0时以上结果仍会出现，具体原因是什么？
2、第一次查询要14秒之久，请问有什么有效的办法优化请给予适当的提示，另附explain结果？
id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, <derived2>, , ALL, , , , , 11279080, 100.00,
2, DERIVED, A, , index, PRIMARY,nid,pid,cid,tid,fbid,sbid,fa,name,faddress, PRIMARY, 38, , 33306, 100.00, Using where
2, DERIVED, <derived3>, , ref, <auto_key0>, <auto_key0>, 38, test.A.id, 338, 100.00,
3, DERIVED, <derived4>, , ALL, , , , , 11279080, 100.00, Using temporary; Using filesort
4, DERIVED, A, , ALL, , , , , 112784, 100.00, Using where
4, DERIVED, <derived5>, , ref, <auto_key0>, <auto_key0>, 4, test.A.id, 10, 100.00,
4, DERIVED, <derived6>, , ref, <auto_key0>, <auto_key0>, 4, test.A.id, 10, 100.00,
6, DERIVED, t_customer_talk, , index, rid, rid, 4, , 4750, 100.00, Using where
5, DERIVED, t_customer_activety, , index, repid, repid, 4, , 149199, 100.00, Using where
以上，请指教，谢谢各位