1

I have the following query:

explain select a.account_number
from account a
join account_draft d on a.account_id = d.account_id
join live_services_transaction as l on l.from_account_id = a.account_id
    and l.posting_date between '2018-05-30' and '2018-06-29'
    and l.amount > 0.00
limit 5000;

which when I do an explain I get the following: enter image description here

When I then remove the one join I get a much better explain that is much more realistic:

explain select a.account_number
from account a
join live_services_transaction as l on l.from_account_id = a.account_id
        and l.posting_date between '2018-05-30' and '2018-06-29'
        and l.amount > 0.00
limit 5000;

Then I get results like this in the explain:

enter image description here

It's really strange to me that I lose the 3028332 records of the l table which I'm expecting. The result of this join is that the query takes FOREVER fetching the results. if I do a limit of 100 it's ok, even 1000 but if I try and return 50000 it never returns.

The create statement for the three tables involved above are:

CREATE TABLE `account` (
  `account_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `version` int(10) unsigned NOT NULL DEFAULT '0',
  `discriminator` enum('C','D','L','S','U') NOT NULL,
  `member_number` int(10) unsigned NOT NULL,
  `account_type` char(4) NOT NULL,
  `sequence` tinyint(2) unsigned zerofill NOT NULL,
  `account_number` char(14) NOT NULL,
  `description` varchar(20) DEFAULT NULL,
  `date_opened` date DEFAULT NULL,
  `date_closed` date DEFAULT NULL,
  `last_activity` date DEFAULT NULL,
  `current_balance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `prev_cutoff_timestamp` timestamp NULL DEFAULT NULL,
  `prev_cutoff_timestamp2` timestamp NULL DEFAULT NULL,
  `access_control` enum('','B','R') DEFAULT NULL,
  `status_message` varchar(40) DEFAULT NULL,
  `collection_queue_id` int(10) unsigned DEFAULT NULL,
  `sort_order` tinyint(2) unsigned DEFAULT NULL,
  `charge_off_date` date DEFAULT NULL,
  `negative_date` date DEFAULT NULL,
  `account_close_reason_id` int(10) unsigned DEFAULT NULL,
  `created_timestamp` timestamp NULL DEFAULT NULL,
  `created_by_userid` char(3) DEFAULT NULL,
  `modified_timestamp` timestamp NULL DEFAULT NULL,
  `modified_by_userid` char(3) DEFAULT NULL,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `member_number_account_type_sequence` (`member_number`,`account_type`,`sequence`),
  UNIQUE KEY `account_number` (`account_number`),
  KEY `fk_account_member_number` (`member_number`),
  KEY `discriminator` (`discriminator`,`member_number`),
  KEY `account_type` (`account_type`),
  KEY `fk_account_close_reason_idx` (`account_close_reason_id`),
  KEY `fk_acct_collection_queue_idx` (`collection_queue_id`),
  CONSTRAINT `fk_account_close_reason` FOREIGN KEY (`account_close_reason_id`) REFERENCES `account_close_reason` (`account_close_reason_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_member_number` FOREIGN KEY (`member_number`) REFERENCES `member` (`member_number`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_acct_collection_queue` FOREIGN KEY (`collection_queue_id`) REFERENCES `collection_queue` (`collection_queue_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=181100 DEFAULT CHARSET=latin1;

and

CREATE TABLE `account_draft` (
  `account_id` int(10) unsigned NOT NULL,
  `check_digit` bigint(10) unsigned zerofill DEFAULT NULL,
  `service_charge` tinyint(1) DEFAULT NULL,
  `draft_fee_id` int(10) unsigned DEFAULT NULL,
  `od_transfer_count` smallint(5) unsigned DEFAULT NULL,
  `withdrawal_count` smallint(5) unsigned DEFAULT NULL,
  `dividend_last` decimal(15,2) NOT NULL DEFAULT '0.00',
  `dividend_ytd` decimal(15,2) NOT NULL DEFAULT '0.00',
  `dividend_lytd` decimal(15,2) NOT NULL DEFAULT '0.00',
  `dividend_calc_timestamp` timestamp NULL DEFAULT NULL,
  `withholding_ytd` decimal(15,2) NOT NULL DEFAULT '0.00',
  `withholding_lytd` decimal(15,2) NOT NULL DEFAULT '0.00',
  `insured_balance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `pledged_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `ls_class` char(1) DEFAULT NULL,
  `ls_status` char(1) DEFAULT NULL,
  `regd_count` smallint(5) unsigned DEFAULT '0',
  `od1_account_id` int(10) unsigned DEFAULT NULL,
  `od2_account_id` int(10) unsigned DEFAULT NULL,
  `od3_account_id` int(10) unsigned DEFAULT NULL,
  `od4_account_id` int(10) unsigned DEFAULT NULL,
  `check_pay_grade` enum('','A','B','C','D','N') DEFAULT NULL,
  `last_check_number` int(10) unsigned DEFAULT NULL,
  `use_check_register` tinyint(1) DEFAULT NULL,
  `check_dep_count` smallint(5) unsigned DEFAULT NULL,
  `drafts_cleared_count` smallint(5) unsigned DEFAULT NULL,
  `prev_check_dep_count` smallint(5) unsigned DEFAULT NULL,
  `prev_drafts_cleared_count` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_jan` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_feb` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_mar` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_apr` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_may` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_jun` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_jul` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_aug` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_sep` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_oct` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_nov` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_dec` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_12m_total` smallint(5) unsigned DEFAULT NULL,
  `returned_drafts_lifetime` smallint(5) unsigned DEFAULT NULL,
  `dividend_rate_id` int(10) unsigned DEFAULT NULL,
  `last_apye_paid` decimal(15,2) NOT NULL DEFAULT '0.00',
  `member_relation_code_id` int(11) DEFAULT NULL,
  `other_fee_1_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_2_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_3_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_4_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_5_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_6_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_7_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_8_count` smallint(5) unsigned DEFAULT NULL,
  `other_fee_9_count` smallint(5) unsigned DEFAULT NULL,
  `minimum_balance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `reg_e_opt_in` enum('UNKNOWN','OPTED-IN','OPTED-OUT') NOT NULL DEFAULT 'UNKNOWN',
  `reg_e_opt_in_date` date DEFAULT NULL,
  `reg_e_opt_in_method` enum('InPerson','Phone','Online','Mail') DEFAULT NULL,
  `reg_e_opt_in_user` char(3) DEFAULT NULL,
  `last_ach_od_fee_date` date DEFAULT NULL,
  `last_sd_od_fee_date` date DEFAULT NULL,
  `last_reg_od_fee_date` date DEFAULT NULL,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `check_digit` (`check_digit`),
  KEY `fk_account_draft_account_id` (`account_id`),
  KEY `fk_account_draft_od1_account_id` (`od1_account_id`),
  KEY `fk_account_draft_od2_account_id` (`od2_account_id`),
  KEY `fk_account_draft_od3_account_id` (`od3_account_id`),
  KEY `fk_account_draft_od4_account_id` (`od4_account_id`),
  KEY `fk_account_draft_draft_fee_id` (`draft_fee_id`),
  KEY `fk_account_draft_member_relation_code_id` (`member_relation_code_id`),
  KEY `fk_account_draft_dividend_rate_id` (`dividend_rate_id`),
  CONSTRAINT `fk_account_draft_account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`account_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_draft_dividend_rate_id` FOREIGN KEY (`dividend_rate_id`) REFERENCES `dividend_rate` (`dividend_rate_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_draft_draft_fee_id` FOREIGN KEY (`draft_fee_id`) REFERENCES `draft_fee` (`draft_fee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_draft_member_relation_code_id` FOREIGN KEY (`member_relation_code_id`) REFERENCES `member_relation_code` (`member_relation_code_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_draft_od1_account_id` FOREIGN KEY (`od1_account_id`) REFERENCES `account` (`account_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_draft_od2_account_id` FOREIGN KEY (`od2_account_id`) REFERENCES `account` (`account_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_draft_od3_account_id` FOREIGN KEY (`od3_account_id`) REFERENCES `account` (`account_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_draft_od4_account_id` FOREIGN KEY (`od4_account_id`) REFERENCES `account` (`account_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

CREATE TABLE `live_services_transaction` (
  `live_services_transaction_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `version` int(11) NOT NULL DEFAULT '0',
  `source` enum('ATM','Credit','Debit','Internet','Mobile','Voice','SB') NOT NULL DEFAULT 'ATM',
  `source_desc` varchar(255) DEFAULT NULL,
  `operation` enum('Inquiry','Update','Action','Withdrawal','Deposit','Transfer') NOT NULL DEFAULT 'Inquiry',
  `operation_desc` varchar(255) DEFAULT NULL,
  `authorization_flag` tinyint(1) NOT NULL DEFAULT '0',
  `status` enum('Pending','Posted','Completed','Error','Declined','Critical') NOT NULL DEFAULT 'Pending',
  `member_number` int(10) unsigned DEFAULT NULL,
  `from_account_id` int(10) unsigned DEFAULT NULL,
  `from_current_balance` decimal(9,2) DEFAULT NULL,
  `from_available` decimal(9,2) DEFAULT NULL,
  `to_account_id` int(10) unsigned DEFAULT NULL,
  `to_current_balance` decimal(9,2) DEFAULT NULL,
  `to_available` decimal(9,2) DEFAULT NULL,
  `card_number` varchar(255) DEFAULT NULL,
  `plastic_number` int(5) DEFAULT NULL,
  `amount` decimal(9,2) DEFAULT NULL,
  `posting_date` date DEFAULT NULL,
  `transaction_desc` varchar(255) DEFAULT NULL,
  `message` varchar(500) DEFAULT NULL,
  `message_suffix` varchar(45) DEFAULT NULL,
  `misc_info` varchar(4) DEFAULT NULL,
  `exempt_atm_flag` tinyint(1) NOT NULL DEFAULT '0',
  `own_atm_flag` tinyint(1) NOT NULL DEFAULT '0',
  `reversal_flag` tinyint(1) NOT NULL DEFAULT '0',
  `adjustment_flag` tinyint(1) NOT NULL DEFAULT '0',
  `recurring_flag` tinyint(1) NOT NULL DEFAULT '0',
  `sig_transaction` tinyint(1) NOT NULL DEFAULT '0',
  `cash_deposit_flag` tinyint(1) NOT NULL DEFAULT '0',
  `exception_flag` tinyint(1) NOT NULL DEFAULT '0',
  `original_amount` decimal(9,2) DEFAULT NULL,
  `surcharge_fee` decimal(9,2) DEFAULT NULL,
  `out_of_network_fee` decimal(9,2) DEFAULT NULL,
  `out_of_network_fee_message` varchar(45) DEFAULT NULL,
  `isa_fee_included` decimal(9,2) DEFAULT NULL,
  `isa_fee_to_settle` decimal(9,2) DEFAULT NULL,
  `isa_fee_message` varchar(45) DEFAULT NULL,
  `cross_border_fee_to_settle` decimal(9,2) DEFAULT NULL,
  `cross_border_fee_message` varchar(45) DEFAULT NULL,
  `auth_hold_days` int(11) DEFAULT NULL,
  `auth_hold_hours` int(11) DEFAULT NULL,
  `auth_hold_minutes` int(11) DEFAULT NULL,
  `efunds_trace_nbr` varchar(44) DEFAULT NULL,
  `acqr_bin` varchar(11) DEFAULT NULL,
  `b02_pan` varchar(255) DEFAULT NULL,
  `b11_trace_nbr` varchar(6) DEFAULT NULL,
  `b13_local_dt` varchar(4) DEFAULT NULL,
  `b18_merchant` varchar(4) DEFAULT NULL,
  `b37_retr_ref` varchar(12) DEFAULT NULL,
  `b38_auth_id` varchar(6) DEFAULT NULL,
  `batch_number` varchar(20) DEFAULT NULL,
  `mult_clearing_seq_number` int(2) DEFAULT NULL,
  `cash_in` decimal(9,2) DEFAULT NULL,
  `cash_out` decimal(9,2) DEFAULT NULL,
  `checks_in` decimal(9,2) DEFAULT NULL,
  `processing_code` char(6) DEFAULT NULL,
  `force_post` tinyint(1) NOT NULL DEFAULT '0',
  `approved_by_vendor` tinyint(1) NOT NULL DEFAULT '0',
  `created_timestamp` timestamp NULL DEFAULT NULL,
  `created_by_userid` char(3) DEFAULT NULL,
  `modified_timestamp` timestamp NULL DEFAULT NULL,
  `modified_by_userid` char(3) DEFAULT NULL,
  PRIMARY KEY (`live_services_transaction_id`),
  KEY `idx_member_number` (`member_number`),
  KEY `idx_status` (`status`),
  KEY `idx_card_number_created_timestamp` (`card_number`,`created_timestamp`),
  KEY `idx_from_account_id_status` (`from_account_id`,`status`),
  KEY `idx_to_account_id_status` (`to_account_id`,`status`),
  KEY `source` (`source`),
  KEY `created_timestamp` (`created_timestamp`),
  KEY `idx_posting_date` (`posting_date`),
  KEY `idx_batch_number` (`batch_number`),
  KEY `idx_status_created` (`status`,`created_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=77898209 DEFAULT CHARSET=latin1;

I know it's a lot of code...thanks for taking the time to help out.

  • actually as I'm still messing with it trying to figure it out...I notice that I don't even have to join to the other table to get it to be strange. – Uncle Iroh Jun 26 '18 at 23:58
  • if I add the following instead to the where clause it also comes up with a strange explain.....d.dividend_rate_id in (32,34,33,8) and – Uncle Iroh Jun 26 '18 at 23:59
  • so it seems to be the dividend_rate_id index on the account_draft table...I'm just not sure why. – Uncle Iroh Jun 27 '18 at 0:01
  • Having 3 unique keys on a table is 'abnormal'. – Rick James Jun 28 '18 at 17:16
  • The second EXPLAIN no longer matches the query. And please make it clear which query is slow. – Rick James Jun 28 '18 at 17:21
2

Try both queries after adding these to live_services_transaction:

 INDEX(posting_date, amount, from_account_id)
 INDEX(from_account_id, posting_date)

What is the setting of innodb_buffer_pool_size? How big (GB) are the tables? Please provide SHOW CREATE TABLE live_services_transaction`.

Another

Instead of

JOIN account_draft d on a.account_id = d.account_id

try

WHERE EXISTS ( SELECT * FROM account_draft
                   WHERE account_id = a.account_id )
  • innodb_buffer_pool_size is 2147483648 (2GB), I also updated the question with a simplified example of the problem. – Uncle Iroh Jun 28 '18 at 16:22
  • See if my addition helps. – Rick James Jun 29 '18 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.