skip to Main Content

The below query finds one id by using the below query format but it seems I have indexed all columns, but it took time to execute between 54.3 seconds – 1.2 mins. What can I try next?

Query:

select 
  op.ord_prod_id 
FROM 
  ordered_product op, 
  payment_type pt, 
  account a, 
  service s, 
  payment_method pm, 
  acct_order ao, 
  acct_order_item aoi 
WHERE 
  (
    (
      op.type = 1 
      OR op.type = 2
    ) 
    AND (
      pt.description = 'Levis' 
      AND op.validity_end_date < '2022-09-18 11:24:08 Etc/GMT' 
      AND op.validity_end_date >= '2022-09-18 00:00:00 Etc/GMT' 
      AND op.acct_status_id = 1 
      AND op.is_Renewable = 'T' 
      AND a.bu_id = 103
    )
  ) 
  AND pm.type_id = pt.id 
  AND s.acct_id = a.acct_id 
  AND op.serv_id = s.serv_id 
  AND ao.pay_meth_id = pm.pay_method_id 
  AND aoi.order_id = ao.order_id 
  AND aoi.ord_prod_id = op.ord_prod_id 
ORDER BY 
  op.validity_end_date ASC

explain plan

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pt
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_pmttype_descrip,idx_description
          key: idx_pmttype_descrip
      key_len: 32
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pm
   partitions: NULL
         type: ref
possible_keys: PRIMARY,payment_method_type_id,Idx_type_id,idx_type_id_zip_code_last_digits
          key: payment_method_type_id
      key_len: 10
          ref: ccbuser.pt.id
         rows: 3297
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: ao
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_pay_meth_id
          key: idx_pay_meth_id
      key_len: 11
          ref: ccbuser.pm.pay_method_id
         rows: 2
     filtered: 100.00
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: aoi
   partitions: NULL
         type: ref
possible_keys: idx_accttorderitem_orderid,idx_ord_prod_id
          key: idx_accttorderitem_orderid
      key_len: 10
          ref: ccbuser.ao.order_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: op
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_ord_prod_servid,idx_ord_prod_statprod,idx_ordprod_type,idx_ord_prod_ve_date,idx_ord_prod_acct_status_id,idx_combo,idx_is_renewable,idx_validity_package_ind_acct_status
          key: PRIMARY
      key_len: 10
          ref: ccbuser.aoi.ord_prod_id
         rows: 1
     filtered: 5.00
        Extra: Using where
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_service_acctid
          key: PRIMARY
      key_len: 10
          ref: ccbuser.op.serv_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_account_buidasid,idx_combo,idx_bu_id,idx_1
          key: PRIMARY
      key_len: 10
          ref: ccbuser.s.acct_id
         rows: 1
     filtered: 5.00
        Extra: Using where
7 rows in set, 11 warnings (0.03 sec)

i have given show warnings;

*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 11:24:08 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 00:00:00 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 3. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 11:24:08 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 4. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 11:24:08 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 5. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 11:24:08 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 6. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 11:24:08 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 7. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 00:00:00 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 8. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 00:00:00 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 9. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 00:00:00 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 10. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2022-09-18 00:00:00 Etc/GMT' for column 'validity_end_date' at row 1
*************************** 11. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select 
  .`op`.`ord_prod_id` AS `ord_prod_id` 
from 
  `ordered_product` `op` 
  join `payment_type` `pt` 
  join `account` `a` 
  join `service` `s` 
  join `payment_method` `pm` 
  join `acct_order` `ao` 
  join `acct_order_item` `aoi` 
where 
  (
    (
      `op`.`ord_prod_id` = `aoi`.`ord_prod_id`
    ) 
    and (
      `aoi`.`order_id` = `ao`.`order_id`
    ) 
    and (
      `ao`.`pay_meth_id` = `pm`.`pay_method_id`
    ) 
    and (
      `s`.`serv_id` = `op`.`serv_id`
    ) 
    and (
      `a`.`acct_id` = `s`.`acct_id`
    ) 
    and (
      `pm`.`type_id` = `pt`.`id`
    ) 
    and (
      `op`.`is_Renewable` = 'T'
    ) 
    and (
      `pt`.`description` = 'Credit Card'
    ) 
    and (
      (`op`.`type` = 1) 
      or (`op`.`type` = 2)
    ) 
    and (
      `op`.`validity_end_date` < '2022-09-18 11:24:08 Etc/GMT'
    ) 
    and (
      `op`.`validity_end_date` >= '2022-09-18 00:00:00 Etc/GMT'
    ) 
    and (
      `op`.`acct_status_id` = 1
    ) 
    and (`a`.`bu_id` = 103)
  ) 
order by 
  `op`.`validity_end_date`

All the rows scan one row, some of them scan 3k for one row – do I have to use str_to_date functions for the above one, are there any ways to improve performance?

Table structure:

       Table: ordered_product
Create Table: CREATE TABLE `ordered_product` (
  `acct_status_id` decimal(22,0) NOT NULL,
  `anual_prepay_ind` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `bill_end_date` datetime DEFAULT NULL,
  `bill_start_date` datetime DEFAULT NULL,
  `equip_id` decimal(22,0) DEFAULT NULL,
  `ord_prod_id` decimal(22,0) NOT NULL,
  `package_id` decimal(22,0) DEFAULT NULL,
  `package_ind` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `parent_ord_prod_id` decimal(22,0) DEFAULT NULL,
  `prod_id` decimal(22,0) DEFAULT NULL,
  `quantity` decimal(38,0) DEFAULT NULL,
  `remove_flag` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `serv_id` decimal(22,0) NOT NULL,
  `srv_end_date` datetime DEFAULT NULL,
  `srv_start_date` datetime DEFAULT NULL,
  `split_prod_id` decimal(22,0) DEFAULT NULL,
  `term` decimal(22,0) DEFAULT NULL,
  `type` decimal(22,0) DEFAULT NULL,
  `tax_charged` decimal(20,6) DEFAULT NULL,
  `comments` varchar(4000) DEFAULT NULL,
  `reason_code_id` decimal(20,0) DEFAULT NULL,
  `price_charged` decimal(20,2) DEFAULT NULL,
  `validity_period` varchar(20) DEFAULT NULL,
  `validity_duration` decimal(38,0) DEFAULT NULL,
  `validity_end_date` datetime DEFAULT NULL,
  `coupon_amount` decimal(20,2) DEFAULT NULL,
  `coupon_code` varchar(100) DEFAULT NULL,
  `applied_wallet_amount` decimal(20,2) DEFAULT NULL,
  `applied_rate_type` varchar(100) DEFAULT NULL,
  `partner_domain` varchar(100) DEFAULT NULL,
  `is_Renewable` varchar(1) DEFAULT NULL,
  `coupon_external_id` varchar(256) DEFAULT NULL,
  `validity_extended_date` datetime DEFAULT NULL,
  `cancellation_date` datetime DEFAULT NULL,
  `rec_indicator` varchar(1) DEFAULT NULL,
  `suspend_start_date` datetime DEFAULT NULL,
  `suspend_end_date` datetime DEFAULT NULL,
  `suspend_duration` decimal(20,0) DEFAULT NULL,
  `commitment_end_date` datetime DEFAULT NULL,
  `modified_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `contact_id` varchar(30) DEFAULT NULL,
  `subscription_status_id` decimal(22,0) DEFAULT NULL,
  `bu_charged_currency_id` decimal(38,0) DEFAULT NULL,
  `currency_code` varchar(200) DEFAULT NULL,
  `legacy_id` varchar(200) DEFAULT NULL,
  `app_service_id` varchar(200) DEFAULT NULL,
  `app_channel_name` varchar(200) DEFAULT NULL,
  `applied_prod_rate_id` decimal(22,0) DEFAULT NULL,
  `applied_prod_rate_in_charged_currency_id` decimal(22,0) DEFAULT NULL,
  `applied_prod_price` decimal(22,0) DEFAULT NULL,
  `bill_to_parent` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`ord_prod_id`),
  KEY `idx_ord_prod_servid` (`serv_id`),
  KEY `idx_ord_prod_statprod` (`acct_status_id`,`prod_id`,`package_id`),
  KEY `idx_ord_prod_bs_date` (`bill_start_date`),
  KEY `idx_ord_prod_be_date` (`bill_end_date`),
  KEY `ordered_product_modified_ts` (`modified_ts`),
  KEY `idx_ordprod_type` (`type`),
  KEY `idx_ord_prod_ve_date` (`validity_end_date`),
  KEY `idx_ord_prod_prod_id` (`prod_id`),
  KEY `idx_ord_prod_srv_start_date` (`srv_start_date`),
  KEY `idx_ord_prod_pack_id` (`package_id`),
  KEY `idx_ord_prod_acct_status_id` (`acct_status_id`),
  KEY `idx_ord_prod_validity_extend_dt` (`validity_extended_date`),
  KEY `idx_remove_flag` (`remove_flag`),
  KEY `idx_ord_prod_srv_end_date` (`srv_end_date`),
  KEY `idx_subscription_status_id` (`subscription_status_id`),
  KEY `idx_coupon_code` (`coupon_code`),
  KEY `idx_parent_ord_prod_id` (`parent_ord_prod_id`),
  KEY `idx_combo` (`type`,`serv_id`),
  KEY `idx_is_renewable` (`is_Renewable`),
  KEY `idx_package_ind` (`package_ind`),
  KEY `idx_validity_package_ind_acct_status` (`validity_end_date`,`package_ind`,`acct_status_id`),
  KEY `idx_legacyid` (`legacy_id`),
  KEY `idx_cancellation_date` (`cancellation_date`),
  KEY `idx_suspend_start_date` (`suspend_start_date`),
  KEY `idx_suspend_end_date_validity_end_date_combo` (`suspend_end_date`,`validity_end_date`,`acct_status_id`,`is_Renewable`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


       Table: payment_type
Create Table: CREATE TABLE `payment_type` (
  `auto_pay_capable_ind` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `can_post_credit_ind` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `check_number_ind` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `description` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `display_order` decimal(22,0) NOT NULL,
  `id` decimal(22,0) NOT NULL,
  `requires_gateway_ind` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `display_name` varchar(30) DEFAULT NULL,
  `modified_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `image_url` varchar(4096) DEFAULT NULL,
  `short_code` varchar(20) DEFAULT NULL,
  `payment_group` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pmttype_descrip` (`description`),
  KEY `idx_display_name` (`display_name`),
  KEY `idx_description` (`description`),
  KEY `idx_payment_group` (`payment_group`),
  KEY `idx_modified_ts` (`modified_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


       Table: account
Create Table: CREATE TABLE `account` (
  `acct_id` decimal(22,0) NOT NULL,
  `acct_status_id` decimal(22,0) NOT NULL,
  `acct_type_id` decimal(22,0) DEFAULT NULL,
  `attach_bill_cycle` char(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `bb_acct_id` varchar(256) DEFAULT NULL,
  `bill_del_type_id` decimal(22,0) DEFAULT NULL,
  `acct_bill_mode` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `bu_id` decimal(22,0) NOT NULL,
  `acct_comment` varchar(500) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `compliment_acct` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `contact_id` decimal(22,0) NOT NULL,
  `corporate_id` decimal(22,0) DEFAULT NULL,
  `create_date` datetime NOT NULL,
  `customer_id` varchar(100) DEFAULT NULL,
  `direct_acct` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `do_not_auto_script` char(5) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `email_inv_format` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `external_id_prefix` varchar(6) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `external_id_suffix` varchar(25) DEFAULT NULL,
  `inter_pic_id` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `intra_pic_id` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `legacy_id` decimal(22,0) DEFAULT NULL,
  `parent_id` decimal(22,0) DEFAULT NULL,
  `partner_id` decimal(22,0) DEFAULT NULL,
  `psa_acct_id` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `acct_reg_num` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `sales_method` varchar(100) DEFAULT NULL,
  `shipping_add_id` decimal(22,0) DEFAULT NULL,
  `tax_exempt` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `tax_number` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `tv_dma_id` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `status_change_date` datetime DEFAULT NULL,
  `external_creation_date` date DEFAULT NULL,
  `sales_person` varchar(200) DEFAULT NULL,
  `offer_type` varchar(50) DEFAULT NULL,
  `cancel_reasons_id` int(38) DEFAULT NULL,
  `acct_addl_info_id` decimal(38,0) DEFAULT NULL,
  `vpn_id` decimal(22,0) DEFAULT NULL,
  `verified` varchar(1) DEFAULT NULL,
  `verified_channel` varchar(50) DEFAULT NULL,
  `active_logins_count` decimal(20,0) DEFAULT NULL,
  `acct_role_id` decimal(20,0) DEFAULT NULL,
  `mso_id` varchar(200) DEFAULT NULL,
  `mso_name` varchar(200) DEFAULT NULL,
  `anonymized` varchar(1) DEFAULT NULL,
  `parental_control` varchar(1) DEFAULT NULL,
  `act_state_id` decimal(38,0) DEFAULT NULL,
  `verified_date` datetime DEFAULT NULL,
  `complaintmode_acct` varchar(1) DEFAULT NULL,
  `modified_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mobile_verified` varchar(1) DEFAULT NULL,
  `acct_sub_type_id` decimal(22,0) DEFAULT NULL,
  `created_by` decimal(22,0) DEFAULT NULL,
  `ip_address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`acct_id`),
  KEY `idx_account_customer_id` (`customer_id`),
  KEY `idx_account_buidasid` (`bu_id`,`acct_status_id`),
  KEY `idx_account_bbaccountid` (`bb_acct_id`),
  KEY `idx_create_date` (`create_date`),
  KEY `idx_contact_id` (`contact_id`),
  KEY `idx_attach_bill_cycle` (`attach_bill_cycle`),
  KEY `idx_act_state` (`act_state_id`),
  KEY `idx_accountt_legacyid` (`legacy_id`),
  KEY `account_modified_ts` (`modified_ts`),
  KEY `idx_account_acctroleid` (`acct_role_id`),
  KEY `idx_extrnl_suffix` (`external_id_suffix`),
  KEY `idx_extrnl_prefix` (`external_id_prefix`),
  KEY `idx_combo` (`bu_id`,`psa_acct_id`),
  KEY `idx_bu_id` (`bu_id`),
  KEY `idx_acct_reg_num` (`acct_reg_num`),
  KEY `idx_acct_stat_id` (`acct_status_id`),
  KEY `account_idx_combo4` (`tax_number`,`acct_id`,`contact_id`,`bu_id`),
  KEY `idx_1` (`bu_id`,`customer_id`),
  KEY `idx_acct_type_id` (`acct_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



       Table: service
Create Table: CREATE TABLE `service` (
  `acct_id` decimal(22,0) NOT NULL,
  `acct_status_id` decimal(22,0) DEFAULT NULL,
  `do_not_auto_script` char(5) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `last_billed` datetime DEFAULT NULL,
  `loc_id` decimal(22,0) DEFAULT NULL,
  `serv_id` decimal(22,0) NOT NULL,
  `start_date` datetime NOT NULL,
  `type` decimal(22,0) DEFAULT NULL,
  `suspend_start_date` date DEFAULT NULL,
  `suspend_end_date` date DEFAULT NULL,
  `pending_suspension_date` date DEFAULT NULL,
  `reactivation_date` date DEFAULT NULL,
  `suspend_duration` decimal(20,0) DEFAULT NULL,
  `modified_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`serv_id`),
  KEY `idx_service_acctid` (`acct_id`),
  KEY `idx_service_locid` (`loc_id`),
  KEY `service_modified_ts` (`modified_ts`),
  KEY `idx_service_modified_ts` (`modified_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


       Table: payment_method
Create Table: CREATE TABLE `payment_method` (
  `account_number` varchar(200) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `bob_id` decimal(22,0) DEFAULT NULL,
  `closed_date` datetime DEFAULT NULL,
  `pay_method_id` decimal(22,0) NOT NULL,
  `start_date` datetime NOT NULL,
  `type_id` decimal(22,0) NOT NULL,
  `credit_card_type_id` decimal(22,0) DEFAULT NULL,
  `expiration_date` datetime DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `pay_meth_type_id` decimal(22,0) NOT NULL,
  `security_code` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `str_addr` varchar(250) DEFAULT NULL,
  `zip_code` varchar(10) DEFAULT NULL,
  `acct_type_id` decimal(22,0) DEFAULT NULL,
  `bank_name` varchar(512) DEFAULT NULL,
  `bank_phone` varchar(15) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `rtn` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `drivers_license` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `email` varchar(256) DEFAULT NULL,
  `sent_out` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `ssn` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `state` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `country` varchar(30) DEFAULT NULL,
  `storage_token` varchar(200) DEFAULT NULL,
  `payer_id` varchar(50) DEFAULT NULL,
  `last_digits` varchar(4) DEFAULT NULL,
  `ref_number` varchar(50) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `dc_code` varchar(20) DEFAULT NULL,
  `xid` varchar(250) DEFAULT NULL,
  `eci` varchar(5) DEFAULT NULL,
  `cavv` varchar(250) DEFAULT NULL,
  `ucaf_authentication_data` varchar(250) DEFAULT NULL,
  `ucaf_collection_ind` varchar(5) DEFAULT NULL,
  `subscription_id` varchar(50) DEFAULT NULL,
  `effort_id` decimal(10,0) DEFAULT NULL,
  `receipt_data` longtext,
  `transaction_id` varchar(512) DEFAULT NULL,
  `org_transaction_id` varchar(512) DEFAULT NULL,
  `tax_area_id` varchar(30) DEFAULT NULL,
  `modified_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `nick_name` varchar(100) DEFAULT NULL,
  `iin` varchar(8) DEFAULT NULL,
  `allow_unlimited` tinyint(1) DEFAULT NULL,
  `name_of_bank` varchar(100) DEFAULT NULL,
  `card_number` varchar(100) DEFAULT NULL,
  `pin` varchar(20) DEFAULT NULL,
  `card_finger_print` varchar(200) DEFAULT NULL,
  `card_issuer_country` varchar(30) DEFAULT NULL,
  `card_service_provider` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`pay_method_id`),
  KEY `payment_method_modified_ts` (`modified_ts`),
  KEY `idx_pmtmethod_transid` (`transaction_id`),
  KEY `idx_payment_method_start_dt` (`start_date`),
  KEY `payment_method_start_date` (`start_date`),
  KEY `payment_method_bob_id` (`bob_id`),
  KEY `payment_method_type_id` (`type_id`),
  KEY `idx_pm_accountno` (`account_number`),
  KEY `idx_exp_date` (`expiration_date`),
  KEY `idx_org_transaction_id` (`org_transaction_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `Idx_bob_id` (`bob_id`),
  KEY `idx_account_number` (`account_number`),
  KEY `Idx_type_id` (`type_id`),
  KEY `i_modified_ts` (`modified_ts`),
  KEY `idx_last_digits` (`last_digits`),
  KEY `idx_ref_number` (`ref_number`),
  KEY `ix_refer_num` (`ref_number`),
  KEY `idx_zipcode` (`zip_code`),
  KEY `idx_card_fingerprint` (`card_finger_print`),
  KEY `idx_phone_number` (`phone_number`),
  KEY `idx_type_id_zip_code_last_digits` (`type_id`,`zip_code`,`last_digits`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


       Table: acct_order
Create Table: CREATE TABLE `acct_order` (
  `acct_id` decimal(22,0) NOT NULL,
  `ao_wf_instance_id` decimal(22,0) DEFAULT NULL,
  `comm` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `comment_id` decimal(38,0) DEFAULT NULL,
  `external_status` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `house_hold_user` varchar(60) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `legacy_id` varchar(200) DEFAULT NULL,
  `modify_date` datetime NOT NULL,
  `order_id` decimal(22,0) NOT NULL,
  `promise_date` datetime NOT NULL,
  `remove_flag` varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `serv_id` decimal(22,0) DEFAULT NULL,
  `txn_id` decimal(22,0) DEFAULT NULL,
  `workflow_instance_id` decimal(22,0) DEFAULT NULL,
  `external_order_id` varchar(200) DEFAULT NULL,
  `ref_number` varchar(200) DEFAULT NULL,
  `pmt_profile_id` varchar(2048) DEFAULT NULL,
  `pay_meth_id` decimal(22,0) DEFAULT NULL,
  `modified_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `txn_summary_id` int(38) DEFAULT NULL,
  `country` varchar(10) DEFAULT NULL,
  `recon_required` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_acctorder_acctid` (`acct_id`),
  KEY `idx_acctorder_wfiid` (`workflow_instance_id`),
  KEY `idx_acctorder_servid` (`serv_id`),
  KEY `idx_ao_mod_dt` (`modify_date`),
  KEY `idx_ext_id` (`external_order_id`),
  KEY `acct_order_modified_ts` (`modified_ts`),
  KEY `idx_pmt_profile_id` (`pmt_profile_id`),
  KEY `idx_ref_number` (`ref_number`),
  KEY `idx_pay_meth_id` (`pay_meth_id`),
  KEY `idx_txn_id` (`txn_id`),
  KEY `idx_promise_date` (`promise_date`),
  KEY `idx_modified_ts` (`modified_ts`),
  KEY `idx_ao_wf_instance_id` (`ao_wf_instance_id`),
  KEY `idx_acct_id_legacy_id` (`acct_id`,`legacy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


       Table: acct_order_item
Create Table: CREATE TABLE `acct_order_item` (
  `modify_date` datetime NOT NULL,
  `ord_prod_id` decimal(22,0) NOT NULL,
  `order_id` decimal(22,0) NOT NULL,
  `order_item_id` decimal(22,0) NOT NULL,
  `modified_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_item_id`),
  KEY `idx_accttorderitem_orderid` (`order_id`),
  KEY `idx_ord_prod_id` (`ord_prod_id`),
  KEY `acct_order_item_modified_ts` (`modified_ts`),
  KEY `idx_acct_order_item_modified_ts` (`modified_ts`),
  KEY `idx_modified_ts` (`modified_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

3

Answers


  1. Re-arranging your query, you get this…

    SELECT
      op.ord_prod_id 
    FROM 
      account             AS a
    INNER JOIN
      service             AS s
        ON s.acct_id = a.acct_id
    INNER JOIN
      ordered_product     AS op
        ON op.serv_id = s.serv_id
    INNER JOIN
      acct_order_item     AS aoi
        ON aoi.ord_prod_id = op.ord_prod_id
    INNER JOIN
      acct_order          AS ao
        ON aoi.order_id = ao.order_id
    INNER JOIN
      payment_method      AS pm
        ON ao.pay_meth_id = pm.pay_method_id
    INNER JOIN
      payment_type        AS pt
        ON pm.type_id = pt.id
    WHERE 
          a.bu_id = 103
      AND op.type IN (1,2)
      AND op.validity_end_date < '2022-09-18 11:24:08 Etc/GMT' 
      AND op.validity_end_date >= '2022-09-18 00:00:00 Etc/GMT'
      AND op.acct_status_id = 1 
      AND op.is_Renewable = 'T' 
      AND pt.description = 'Levis' 
    ORDER BY 
      op.validity_end_date ASC
    

    This looks wrong to me, but I can’t tell without the database schema.

    Here’s why I think it’s wrong…

    • Get all accounts with bu_id = 103
    • Get all of those accounts’ services (the same service may appear multiple times)
    • Get all ordered products associated to those services regardless of account

    I strongly suspect that acct_order has an acct_id column, which would likely enable something like this…

    SELECT
      op.ord_prod_id 
    FROM 
      account             AS a
    INNER JOIN
      acct_order          AS ao
        ON ao.acct_id = a.acct_id
    
    INNER JOIN
      payment_method      AS pm
        ON pm.pay_method_id = ao.pay_meth_id
    INNER JOIN
      payment_type        AS pt
        ON pt.id = pm.type_id
    
    INNER JOIN
      acct_order_item     AS aoi
        ON aoi.order_id = ao.order_id
    INNER JOIN
      ordered_product     AS op
        ON op.ord_prod_id = aoi.ord_prod_id
    --
    -- No need to join on service now
    --
    WHERE 
          a.bu_id = 103
      AND pt.description = 'Levis' 
      AND op.type IN (1,2)
      AND op.acct_status_id = 1 
      AND op.is_Renewable = 'T' 
      AND op.validity_end_date >= '2022-09-18 00:00:00 Etc/GMT'
      AND op.validity_end_date <  '2022-09-18 11:24:08 Etc/GMT' 
    ORDER BY 
      op.validity_end_date ASC
    

    That alone will likely reduce the number of rows being processed, and give insights into what indexes you want, potentially being…

    account (bu_id, acct_id)
    acct_order (acct_id, pay_meth_id, order_id)
    
    payment_method (pay_method_id, type_id) or (type_id, pay_method_id)
    payment_type (id, description) or (description, id)
    
    acct_order_item (order_id, ord_prod_id)
    ordered_product (type, acct_status_id, is_renewable, validity_end_date, ord_prod_id)
    

    You should also fix the date literal being used in the WHERE clause.

    Either way, if you want a more categorical response, you need to include the full schema for those tables, including indexes and foreign keys (to validate your joins) as well as row counts, cardinality, etc (to direct you on index creation, performance tuning, etc).

    Login or Signup to reply.
  2. Edited to remove description per Rick James’s comment.

    An index designed match your query will make it faster. (But it won’t make it correct if it’s wrong, just sayin’.)

    You filter your ordered_product table on:

    • type (two values)
    • account_status_id (equality)
    • is_Renewable (equality)
    • validity_end_date (date range)

    So try an index with all the equality match columns followed by the range column. MySql can random-access it to the first eligible column and then scan it sequentially. Fast.

    CREATE INDEX desc_stat_renew_date ON ordered_product
         (acct_status_id, is_Renewable, validity_end_date);
    

    To learn about this, see Marcus Winad’s excellent https://use-the-index-luke.com/ .

    Pro tip Using mixed case in column or table names can cause confusion if/when you migrate to another server machine. Avoid!

    Login or Signup to reply.
  3. There are several orders in which the Optimizer might evaluate the query. Here are some additional indexes that the optimizer might use, especially if it chooses to start with op.

    op: INDEX(acct_status_id, is_Renewable, type, validity_end_date, serv_id, ord_prod_id)
    op: INDEX(acct_status_id, is_Renewable, validity_end_date, type, serv_id, ord_prod_id)
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search