Mysql – Adding a join to a table in a query makes it super slow

MySQL

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.

Best Answer

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 )
Related Question