优化查询 求高手 在线等谢谢

Question

sql 如下
select a.xx,
a.yy,
a.vv,
c.name,
 (SELECT bci.DEBIT_ACCOUNT_BALANCE
          FROM business_confirm_info bci
         WHERE bci.application_id = a.application_id
        ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
         LIMIT 1)
          debitAccountBalance
from application a ,CUSTOMER C,
ORDER BY a.CLEARING_DATE DESC;
 主要是那个子查询 因为里面的数据量过大导致查询非常慢 求指教 谢谢

Answer 1

explain SELECT bci.DEBIT_ACCOUNT_BALANCE
           FROM business_confirm_info bci
          WHERE bci.application_id = a.application_id
         ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
          LIMIT 1;看看什么结果

Answer 2

from application a ,CUSTOMER C,
a和c没有连接条件吗   自然连接？

Answer 3

不是要看结果，看执行计划。注意EXPLAIN

Answer 4

取最大的一条的BUSINESS_CONFIRM_INFO_ID 的 内容   比如    id 1   2  3  4  5 
那么 取 第四条 的内容 

Answer 5

desc select a.xx,
 a.yy,
 a.vv,
 c.name,
  (SELECT bci.DEBIT_ACCOUNT_BALANCE
           FROM business_confirm_info bci
          WHERE bci.application_id = a.application_id
         ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
          LIMIT 1)
           debitAccountBalance
 from application a ,CUSTOMER C,
 ORDER BY a.CLEARING_DATE DESC;

Answer 6

SELECT bci.DEBIT_ACCOUNT_BALANCE FROM business_confirm_info bci
where not exists(select 1 from business_confirm_info 
where bci.application_id = application_id 
and bci.BUSINESS_CONFIRM_INFO_ID<BUSINESS_CONFIRM_INFO_ID)需要多长时间，business_confirm_info在application_id、BUSINESS_CONFIRM_INFO_ID上建立复合索引没有？
BUSINESS_CONFIRM_INFO_ID唯一？

Answer 7

Lookup Error - MySQL Database Error: Subquery returns more than 1 row 建立了索引的、

Answer 8

SELECT a.application_no,
       C.CUSTOMER_NO,
       C.NAME,
       (SELECT DEPOSITE_ACCOUNT
          FROM contract_info
         WHERE application_id = a.APPLICATION_ID)
          DEPOSITE_ACCOUNT,
       RPD.CYCLE_NUM,
       (SELECT CYCLES
          FROM repayment_plan
         WHERE REPAYMENT_PLAN_ID = RPD.REPAYMENT_PLAN_ID)
          TOTAL_CYCLES,
       RPD.CLEARING_DATE,
       RPD.TOTAL,
       RPD.PRINCIPAL,
       RPD.INTEREST,
       RPD.EXTEND_MONTH,
       l.advance_no,
       (SELECT if(c.type = 3, company_phone, mobile_phone)
          FROM contact_info
         WHERE contact_info_id = c.contact_info_id)
          phone_num,
       (SELECT name
          FROM branch
         WHERE BRANCH_ID = a.BRANCH_ID)
          BRANCH_NAME,
       (SELECT chinese_name
          FROM users
         WHERE user_id = a.CREDITOR_ID)
          CREDITOR_NAME,
       (SELECT p.PRODUCT_NAME
          FROM product p
         WHERE p.PRODUCT_ID = a.PRODUCT_ID)
          PRODUCT_NAME,
       (SELECT bci.DEBIT_ACCOUNT_BALANCE
          FROM business_confirm_info bci
         WHERE bci.application_id = a.application_id
        ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
         LIMIT 1)
          debitAccountBalance
  FROM REPAYMENT_PLAN_DETAIL RPD,
       LOAN L,
       CUSTOMER C,
       application a,
       repayment r
 WHERE     r.application_id = l.application_id
       AND r.REPAYMENT_PLAN_DETAIL_id = rpd.REPAYMENT_PLAN_DETAIL_id
       AND RPD.application_id = L.APPLICATION_ID
       AND a.CUSTOMER_ID = C.CUSTOMER_ID
       AND L.APPLICATION_ID = a.APPLICATION_ID
       AND a.BANK_ID = 1
       AND RPD.CLEARING_DATE >= DATE('2012-01-01')
       AND RPD.CLEARING_DATE < DATE(adddate('2012-12-31', 1))
       AND a.APPLICATION_STATUS_ID = 13
ORDER BY RPD.CLEARING_DATE DESC

Answer 9

把(SELECT bci.DEBIT_ACCOUNT_BALANCE           FROM business_confirm_info bci          WHERE bci.application_id = a.application_id         ORDER BY BUSINESS_CONFIRM_INFO_ID DESC         LIMIT 1) 拆开 之后用两个查询 把第一个的结果集 获取id 放到第二个去查绝对快