1500条数据和20w数据匹配用150s 求大神帮忙优化一下~~~~~~~Sql
SELECT companynumber,company,AccountNumber,OwnerNumber,OwnerFirstName,OwnerLastName,BillingMonth,InvoiceNumber,
ServiceCategoryType,ServiceType,ConfID,ConfName,Leader,DATE_FORMAT(billing.ConfDate,'%Y-%m-%d') AS ConfDate,LeaderANI,
DATE_FORMAT(billing.ConnectionStartTime,'%H:%m:%s') AS ConnectionStartTime,DATE_FORMAT(billing.ConnectionEndTime,'%H:%m:%s') AS ConnectionEndTime,
Minutes,International,CountryCode,CallDirection,RCFNumber,OriginalANI,ANI,DNIS,ParticipantName,
ChargeCode,InvoiceCharge,InvoiceTax,USDTax,USDCharge,haoma,zhujihaoma,DATE_FORMAT(yunyingshang.hujiaoshijian,'%Y-%m-%d') AS hujiaoshijian,
DATE_FORMAT(yunyingshang.hujiaoshijian,'%H:%m:%s') AS hujiaoshijianshi,
miao,fen,danjia,feiyongheji,ROUND((yunyingshang.fen+yunyingshang.miao/60),2) AS dbMinutes
FROM billing LEFT join yunyingshang on billing.RCFNumber=yunyingshang.haoma
AND DATE_FORMAT(billing.ConfDate,'%Y%m%d')=DATE_FORMAT(yunyingshang.hujiaoshijian,'%Y%m%d')
AND TIMESTAMPDIFF(second,billing.ConnectionStartTime,yunyingshang.hujiaoshijian) BETWEEN -60 and 60
AND (yunyingshang.fen*60+yunyingshang.miao-billing.Minutes*60) BETWEEN -60 AND 60
SELECT companynumber,company,AccountNumber,OwnerNumber,OwnerFirstName,OwnerLastName,BillingMonth,InvoiceNumber,
ServiceCategoryType,ServiceType,ConfID,ConfName,Leader,DATE_FORMAT(billing.ConfDate,'%Y-%m-%d') AS ConfDate,LeaderANI,
DATE_FORMAT(billing.ConnectionStartTime,'%H:%m:%s') AS ConnectionStartTime,DATE_FORMAT(billing.ConnectionEndTime,'%H:%m:%s') AS ConnectionEndTime,
Minutes,International,CountryCode,CallDirection,RCFNumber,OriginalANI,ANI,DNIS,ParticipantName,
ChargeCode,InvoiceCharge,InvoiceTax,USDTax,USDCharge,haoma,zhujihaoma,DATE_FORMAT(yunyingshang.hujiaoshijian,'%Y-%m-%d') AS hujiaoshijian,
DATE_FORMAT(yunyingshang.hujiaoshijian,'%H:%m:%s') AS hujiaoshijianshi,
miao,fen,danjia,feiyongheji,ROUND((yunyingshang.fen+yunyingshang.miao/60),2) AS dbMinutes
FROM billing LEFT join yunyingshang on billing.RCFNumber=yunyingshang.haoma
AND DATE_FORMAT(billing.ConfDate,'%Y%m%d')=DATE_FORMAT(yunyingshang.hujiaoshijian,'%Y%m%d')
AND TIMESTAMPDIFF(second,billing.ConnectionStartTime,yunyingshang.hujiaoshijian) BETWEEN -60 and 60
AND (yunyingshang.fen*60+yunyingshang.miao-billing.Minutes*60) BETWEEN -60 AND 60
AND TIMESTAMPDIFF(second,billing.ConnectionStartTime,yunyingshang.hujiaoshijian) BETWEEN -60 and 60
AND (yunyingshang.fen*60+yunyingshang.miao-billing.Minutes*60) BETWEEN -60 AND 60这些都是通过计算得来的东西。无法利用索引优化。
建议描述一下想实现的功能,或者有其它方式方法。
一些时间的 格式 也可以通过 varchar 类型就行表述