SELECT payment.row_id AS paymentId,
house.row_id AS house_id,
category.`name` AS itemName,
stdset.`name` AS standName,
payment.owner_id,
payment.owner_sf AS owner_status,
house.community_name,
house.group_name,
house.building_name,
house.unit_name,
house.house_no,
payment.should_date,
payment.calc_end_time,
pm_fee_formula.add_month,
SUM(payment.factcharge) AS factcharge,
SUM(payment.realcharge)AS realcharge ,
undercorp.owner_name,
undercorp.contact_way,record.schd,record.bchd,record.yongliang,pm_fee_formula.unit FROM pm_fee_of_leaves_payment AS payment
LEFT JOIN pm_resource_of_house AS house ON house.row_id = payment.house_id
LEFT JOIN pm_resource_index ON pm_resource_index.sub_table_id = house.row_id AND pm_resource_index.sub_table = 'pm_resource_of_house'
LEFT JOIN pm_fee_of_std_set AS stdset ON stdset.row_id = payment.fee_id
LEFT JOIN pm_fee_of_std_category AS category ON category.row_id = stdset.std_category
LEFT JOIN pm_fee_formula ON pm_fee_formula.row_id=payment.fee_calc_id
LEFT JOIN pm_owner_profile_under_corp AS undercorp ON undercorp.row_id = payment.owner_id
LEFT JOIN pm_meter_record AS record ON record.payment_id = payment.row_id WHERE payment.corp_id = 295 AND payment.is_verified = 1 and payment.bank_data_id=0 AND house.community_id = 2 AND ( payment.sfzt=1 OR payment.sfzt=2 ) GROUP BY payment.house_id ,payment.owner_id,payment.fee_id,FROM_UNIXTIME(payment.should_date,"%Y%c") ORDER BY house.house_order, pm_resource_index.parent_id,pm_resource_index.order ASC ,payment.should_date ASC 主要是record表数据较多,导致查询较慢,有没有办法让其record表匹配上较好的索引。
payment.row_id 为主键,int类型
record.payment_id为varchar,形式类似 1,2,3 的形式
试过cast转char,但是会损失掉部分数据,试过concat后再IN(),但是无法走上索引
下面附上explain结果
house.row_id AS house_id,
category.`name` AS itemName,
stdset.`name` AS standName,
payment.owner_id,
payment.owner_sf AS owner_status,
house.community_name,
house.group_name,
house.building_name,
house.unit_name,
house.house_no,
payment.should_date,
payment.calc_end_time,
pm_fee_formula.add_month,
SUM(payment.factcharge) AS factcharge,
SUM(payment.realcharge)AS realcharge ,
undercorp.owner_name,
undercorp.contact_way,record.schd,record.bchd,record.yongliang,pm_fee_formula.unit FROM pm_fee_of_leaves_payment AS payment
LEFT JOIN pm_resource_of_house AS house ON house.row_id = payment.house_id
LEFT JOIN pm_resource_index ON pm_resource_index.sub_table_id = house.row_id AND pm_resource_index.sub_table = 'pm_resource_of_house'
LEFT JOIN pm_fee_of_std_set AS stdset ON stdset.row_id = payment.fee_id
LEFT JOIN pm_fee_of_std_category AS category ON category.row_id = stdset.std_category
LEFT JOIN pm_fee_formula ON pm_fee_formula.row_id=payment.fee_calc_id
LEFT JOIN pm_owner_profile_under_corp AS undercorp ON undercorp.row_id = payment.owner_id
LEFT JOIN pm_meter_record AS record ON record.payment_id = payment.row_id WHERE payment.corp_id = 295 AND payment.is_verified = 1 and payment.bank_data_id=0 AND house.community_id = 2 AND ( payment.sfzt=1 OR payment.sfzt=2 ) GROUP BY payment.house_id ,payment.owner_id,payment.fee_id,FROM_UNIXTIME(payment.should_date,"%Y%c") ORDER BY house.house_order, pm_resource_index.parent_id,pm_resource_index.order ASC ,payment.should_date ASC 主要是record表数据较多,导致查询较慢,有没有办法让其record表匹配上较好的索引。
payment.row_id 为主键,int类型
record.payment_id为varchar,形式类似 1,2,3 的形式
试过cast转char,但是会损失掉部分数据,试过concat后再IN(),但是无法走上索引
下面附上explain结果
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货