MySQL version 5.6.16:
Before optimization:
[Email protected] jd_credit 03:41:23>select AAA. pop_vender_acc_id as Accid, sum (AAA.AA1) as TC
-From (SELECT a.pop_vender_acc_id,
A.pop_vender_name,
A.license_no,
(case
-When a.pop_vender_name = B.pop_vender_name OR
-A.license_no = B.license_no Then
B.totalprincipal
-ELSE
0
-END) Aa1
From A_01_pop_info A
JOIN (SELECT T.pop_vender_name,
T.license_no,
T.POP_VENDER_ACC_ID,
SUM (t1. Rest_principal) as Totalprincipal
From A_01_pop_info T, A_01_pop_loan_info T1
, WHERE t.pop_vender_acc_id = t1. pop_vender_acc_id
and T1. STATUS = ' 1 '
GROUP by t.pop_vender_acc_id,
T.pop_vender_name,
-T.license_no) b on 1 = 1) AAA
where AAA. pop_vender_acc_id = ' 40367 '
GROUP by AAA. pop_vender_acc_id;
+-------+----------+
| Accid | TC |
+-------+----------+
| 40367 | 34817.30 |
+-------+----------+
1 row in Set (9.64 sec)
After optimization:
[Email protected] jd_credit 03:41:16>select AAA. pop_vender_acc_id as Accid, sum (AAA.AA1) as TC
-From (SELECT a.pop_vender_acc_id,
A.pop_vender_name,
A.license_no,
(case
-When a.pop_vender_name = B.pop_vender_name OR
-A.license_no = B.license_no Then
B.totalprincipal
-ELSE
0
-END) Aa1
From A_01_pop_info A
JOIN (SELECT T.pop_vender_name,
T.license_no,
T.POP_VENDER_ACC_ID,
SUM (t1. Rest_principal) as Totalprincipal
From A_01_pop_info T, A_01_pop_loan_info T1
, WHERE t.pop_vender_acc_id = t1. pop_vender_acc_id
and T1. STATUS = ' 1 '
GROUP by t.pop_vender_acc_id,
T.pop_vender_name,
-T.license_no) b on 1 = 1
where a.pop_vender_acc_id = ' 40367 ') AAA
GROUP by AAA. pop_vender_acc_id
;
+-------+----------+
| Accid | TC |
+-------+----------+
| 40367 | 34817.30 |
+-------+----------+
1 row in Set (0.00 sec)
. From the current version of 5.6.16 MySQL does not automatically do a predicate push, Oracle 8i started this aspect of automatic predicate push, reduce the workload of optimization, I hope that MySQL can implement the predicate push function as early as possible.
This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1557138
MySQL SQL cannot auto verb push in