On MySQL 5.7.11 running on Amazon RDS with InnoDB
I have a fairly heavy reporting query that takes about 3 minutes to run. During this time I can't access my reporting screen, which reads some min and max dates from one of the tables that is included in the report.
I would have thought by setting "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" read-only queries would not affect each other at all? I have this set both at session level and my param group on RDS
Is there anything I'm missing?
SHOW ENGINE INNODB STATUS
show create table …
auto_increment number indicate rough row counts
CREATE TABLE `transaction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`file_id` int(11) DEFAULT NULL,
`countid` int(11) NOT NULL,
`txn_date` datetime NOT NULL,
`txn_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`user_rmn` bigint(20) NOT NULL,
`customer_no` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`aggregator_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`trans_amount` decimal(15,4) NOT NULL,
`incoming_commission` decimal(15,4) NOT NULL,
`mmplt_txn_id` int(11) NOT NULL,
`product_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`txn_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`circle` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`role` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`number` int(11) DEFAULT NULL,
`user_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`city_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`state_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`retailer_commission` decimal(15,4) NOT NULL,
`total_commission` decimal(15,4) NOT NULL,
`net_revenue` decimal(15,4) NOT NULL,
`ad_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`ad_commission` decimal(15,4) DEFAULT NULL,
`md_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`md_commission` decimal(15,4) DEFAULT NULL,
`cnf_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`cnf_commission` decimal(15,4) DEFAULT NULL,
`ad_id` bigint(20) DEFAULT NULL,
`md_id` bigint(20) DEFAULT NULL,
`cnf_id` bigint(20) DEFAULT NULL,
`operator_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `txnId` (`txn_id`),
KEY `IDX_723705D193CB796C` (`file_id`),
KEY `date_idx` (`txn_date`),
KEY `user_idx` (`user_id`),
KEY `cnf_idx` (`cnf_id`),
KEY `md_idx` (`md_id`),
KEY `ad_idx` (`ad_id`),
KEY `user_rmn_idx` (`user_rmn`),
KEY `trans_amount_idx` (`trans_amount`),
KEY `incoming_commission_idx` (`incoming_commission`),
KEY `retailer_commission_idx` (`retailer_commission`),
KEY `ad_commission_idx` (`ad_commission`),
KEY `md_commission_idx` (`md_commission`),
KEY `cnf_commission_idx` (`cnf_commission`),
KEY `cnf_date_idx` (`txn_date`,`cnf_id`),
KEY `md_date_idx` (`txn_date`,`md_id`),
KEY `ad_date_idx` (`txn_date`,`ad_id`),
KEY `user_rmn_date_idx` (`txn_date`,`user_rmn`),
CONSTRAINT `FK_723705D193CB796C` FOREIGN KEY (`file_id`) REFERENCES `file_to_sync` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11370410 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `operator` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`category_low_id` int(11) DEFAULT NULL,
`category_medium_id` int(11) DEFAULT NULL,
`category_high_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_D7A6A781B596C062` (`category_low_id`),
KEY `IDX_D7A6A78125326495` (`category_medium_id`),
KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `operator` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`category_low_id` int(11) DEFAULT NULL,
`category_medium_id` int(11) DEFAULT NULL,
`category_high_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_D7A6A781B596C062` (`category_low_id`),
KEY `IDX_D7A6A78125326495` (`category_medium_id`),
KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `operator_category_medium` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `operator_category_high` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `depositor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`depositor_id` bigint(20) NOT NULL,
`name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`amount` decimal(15,4) NOT NULL,
`deposited` datetime NOT NULL,
`details` longtext COLLATE utf8_unicode_ci NOT NULL,
`netsuite_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `depositor_idx` (`depositor_id`),
KEY `netsuite_id_idx` (`netsuite_id`),
KEY `deposited_idx` (`deposited`)
) ENGINE=InnoDB AUTO_INCREMENT=62650 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Queries
DROP temporary TABLE IF EXISTS `depositor_type`;
CREATE temporary TABLE `depositor_type` (
`depositor_id` bigint(20) NOT NULL,
`type` varchar(4) NOT NULL,
amount decimal(20,4) NULL,
count_deposit int(11) NULL,
PRIMARY KEY (depositor_id, type),
KEY type_idx (type),
KEY amount_idx (amount)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into depositor_type (type, depositor_id ) select distinct 'u',user_rmn as depositor_id from transaction where txn_date between :from and :to and user_rmn is not null union DISTINCT select distinct 'a',ad_id as depositor_id from transaction where txn_date between :from and :to and ad_id is not null union DISTINCT select distinct 'm',md_id as depositor_id from transaction where txn_date between :from and :to and md_id is not null union DISTINCT select distinct 'c',cnf_id as depositor_id from transaction where txn_date between :from and :to and cnf_id is not null;
update depositor_type set amount=(select sum(amount) from depositor d where d.depositor_id=depositor_type.depositor_id), count_deposit=(select count(amount) from depositor d where d.depositor_id=depositor_type.depositor_id) ;
DROP TABLE IF EXISTS `9bf92fsums`;
CREATE TABLE `9bf92fsums` ( `cnf_id` bigint(20) NOT NULL,
`md_id` bigint(20) NOT NULL,
`ad_id` bigint(20) NOT NULL,
`user_rmn` bigint(20) NOT NULL,
`operator_id` int(11) not null,
`trans_amount` decimal(20,4) NOT NULL,
`incoming_commission` decimal(20,4) NOT NULL,
`retailer_commission` decimal(20,4) NOT NULL,
`ad_commission` decimal(20,4) NOT NULL,
`md_commission` decimal(20,4) NOT NULL,
`cnf_commission` decimal(20,4) NOT NULL,
`count_trans` int(11) not null,
PRIMARY KEY (`cnf_id`,`md_id`,`ad_id`,`user_rmn`, `operator_id`),
KEY `md_id_idx` (`cnf_id`),
KEY `ad_id_idx` (`ad_id`) USING BTREE,
KEY `user_rmn_idx` (`user_rmn`) USING BTREE,
KEY `operator_id_idx` (`operator_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into 9bf92fsums select distinct coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), count(txn_id) as count_trans from transaction where txn_date between :from and :to group by coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id
select 'User' as type, user_rmn as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.user_rmn=t.depositor_id and t.type='u' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by user_rmn, och.name, ocm.name, ocl.name
select 'AD' as type, ad_id as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.ad_id=t.depositor_id and t.type='a' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by ad_id, och.name, ocm.name, ocl.name
select 'MD' as type, md_id as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.md_id=t.depositor_id and t.type='m' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by md_id, och.name, ocm.name, ocl.name
select 'CNF' as type, cnf_id as phone, t.amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join depositor_type t on s.cnf_id=t.depositor_id and t.type='c' inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0 group by cnf_id, och.name, ocm.name, ocl.name
select distinct 'no deposits' as type, null as depositor_id, 0 as sum_dep_amount, sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission, sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), 0 as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s inner join operator o on s.operator_id=o.id left join operator_category_high och on (o.category_high_id=och.id) left join operator_category_medium ocm on (o.category_medium_id=ocm.id) left join operator_category_low ocl on (o.category_low_id=ocl.id) group by 'no deposits', och.name, ocm.name, ocl.name
select distinct 'no transactions' as type, depositor_id, sum(amount) as sum_dep_amount, 0 as trans_amount, 0 as incoming_commission, 0 as retailer_commission, 0 as ad_commission, 0 as md_commission, 0 as cnf_commission, count(d.id) as cnt_depositors, 0 as cnt_txn_id, 'n/a' as operator_category_high, 'n/a' as operator_category_medium, 'n/a' as operator_category_low from depositor d where depositor_id not in (select user_rmn from 9bf92fsums) and depositor_id not in (select ad_id from 9bf92fsums) and depositor_id not in (select md_id from 9bf92fsums) and depositor_id not in (select cnf_id from 9bf92fsums) group by 'no transactions', depositor_id
Best Answer
Rather than address the question as asked, I will address some optimizations.
latin1andutf8. Charset conversion can prevent usage of indexes.BIGINT(8 bytes)? The "operators" could probably do withTINYINT UNSIGNED(1 byte). Etc.I presume you are augmenting, not rebuilding, the "Summary tables"? More discussion here. Proper use of Summary tables is the main way to get performance from "reports".
Time each statement -- perhaps one of them (the
UPDATEwith the subqueries?) is taking most of the time.