Ask how the SQL optimization, now run time probably more than 8 seconds

CodePudding user response:

Give a word version of the line no

CodePudding user response:

EXPLAIN the SELECT
Zm. Xfdc_index,
The sum (ald. Encash_amt)/100 - sum (
Arld. Paid_prin_amt + arld. Paid_ovd_prin_amt
)/100
The FROM
Cm_zm_das_info zm,
Ants_loan_detail ald LEFT JOIN
(
SELECT
Contract_no,
Business_date,
The sum (paid_prin_amt) AS paid_prin_amt,
The sum (paid_ovd_prin_amt) AS paid_ovd_prin_amt
The FROM
Ants_repay_loan_detail
WHERE
Business_date & lt;
='2019-02-28'GROUP BY
Contract_no
) arld ON ald. Contract_no=arld. Contract_no
WHERE
Ald. Business_date & lt;
='2019-02-28'AND zm. Ic_number=ald. Cert_no
GROUP BY
Zm. Xfdc_index
The order by null;

CodePudding user response:

So the statement the ascension of the space is not large, you can add two indexes try,
Ants_loan_detail (cert_no business_date);
Ants_repay_loan_detail (business_date contract_no);

CodePudding user response:

The two indexes have, but I don't quite understand left connection of time why not use the index, left connection can only be full table query? And use the temporary table, the temporary table can optimize away

CodePudding user response:

Select * from a left join b on b.i d=Anderson d where Anderson, d=? ;
The statement above, on the content of the back, don't go the index of a table, after the where condition, can can go the index of a table,