Search code examples
mysqlquery-optimization

How to optimize the low performance MySQL query

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
Solution

  • 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).