MySQL SQL cannot auto verb push in

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