how to optimize the below query without adding index

Question

the below query has some problems so can we able to optimize the query with the current index format cuz the table size is huge so I don't know what to do.

Query:

select 
  `sale_amount`, 
  `provider_transaction_id`, 
  `id` as `acc_id`, 
  `status`, 
  `service_id`, 
  `provider_status`, 
  `is_sale_settled`, 
  CASE WHEN status = 1 
  and is_sale_settled = 1 then 4 WHEN status = 1 
  and is_sale_settled = 0 then 3 ELSE 5 END AS acc_ics_status, 
  `system_transaction_id`, 
  `engine_transaction_id`, 
  `transaction_date`, 
  `created_at` as `transaction_date_time`, 
  HOUR(created_at) as transaction_hour 
from 
  `st_ret_txn_aeps` 
where 
  `created_at` >= '2022-10-03 14:45:01' 
  and `created_at` <= '2022-10-03 14:48:00' 
  and `service_id` = 14 
  and `transaction_date` = '2022-10-03'

Row scans;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: st_ret_txn_aeps
   partitions: NULL
         type: index_merge
possible_keys: transaction_id_sevrice_id_unique,st_ret_txn_aeps_service_id_index,idx_txn_date
          key: idx_txn_date,st_ret_txn_aeps_service_id_index
      key_len: 3,1
          ref: NULL
         rows: 122328
     filtered: 11.11
        Extra: Using intersect(idx_txn_date,st_ret_txn_aeps_service_id_index); Using where
1 row in set, 1 warning (0.00 sec)

Table structure;

*************************** 1. row ***************************
       Table: st_ret_txn_aeps
Create Table: CREATE TABLE `st_ret_txn_aeps` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `service_id` tinyint(4) NOT NULL,
  `transaction_type_id` tinyint(4) NOT NULL,
  `business_org_id` int(10) unsigned NOT NULL,
  `sub_agent_id` int(11) DEFAULT NULL,
  `agent_id` int(10) unsigned DEFAULT NULL,
  `business_org_user_id` int(10) unsigned NOT NULL,
  `business_org_sub_user_id` int(10) unsigned DEFAULT NULL,
  `retailer_category_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rate_profile_id` int(11) DEFAULT '0',
  `wallet_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `business_org_pan` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_org_gstin` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `operator_id` int(10) unsigned NOT NULL,
  `operator_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `sub_product_id` int(11) DEFAULT NULL,
  `provider_id` int(10) unsigned NOT NULL,
  `provider_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_id` int(11) NOT NULL DEFAULT '0',
  `branch_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sale_amount` double NOT NULL,
  `total_b_org_charges` double NOT NULL,
  `total_b_org_comm_tds` double DEFAULT '0',
  `total_b_org_commission` double NOT NULL,
  `final_debit_value` double NOT NULL,
  `balance_after_txn` double NOT NULL,
  `system_transaction_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `engine_transaction_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `provider_transaction_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `operator_transaction_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_1` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_2` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_3` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_4` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_5` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_org_markup` double NOT NULL DEFAULT '0',
  `refund_date` date DEFAULT NULL,
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_invoice_data_generated` tinyint(1) NOT NULL,
  `status` tinyint(4) NOT NULL COMMENT '1: success ,2: failed',
  `provider_status` tinyint(4) DEFAULT NULL,
  `provider_status_old` tinyint(4) DEFAULT NULL,
  `transaction_date` date NOT NULL,
  `customer_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_mobile_no` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `app_type` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '1:WEBPOS 2:ANDROID',
  `is_cashout_sale` tinyint(1) NOT NULL,
  `is_sale_settled` tinyint(1) NOT NULL,
  `settlement_date` date DEFAULT NULL,
  `analytics_sync_status` tinyint(1) DEFAULT NULL,
  `analytics_sync_status_refund` tinyint(1) DEFAULT NULL,
  `invoice_data_id` int(11) DEFAULT NULL,
  `credit_note_data_id` int(11) DEFAULT NULL,
  `status_updated_at` bigint(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_id_sevrice_id_unique` (`service_id`,`engine_transaction_id`),
  UNIQUE KEY `system_transaction_id_unique` (`system_transaction_id`),
  KEY `st_ret_txn_aeps_service_id_index` (`service_id`),
  KEY `st_ret_txn_aeps_business_org_id_index` (`business_org_id`),
  KEY `st_ret_txn_aeps_agent_id_index` (`agent_id`),
  KEY `st_ret_txn_aeps_business_org_user_id_index` (`business_org_user_id`),
  KEY `st_ret_txn_aeps_system_transaction_id_index` (`system_transaction_id`),
  KEY `st_ret_txn_aeps_refund_date_index` (`refund_date`),
  KEY `idx_txn_date` (`transaction_date`),
  KEY `st_ret_txn_aeps_settlement_date_index` (`settlement_date`),
  KEY `status_updated_at_idx` (`status_updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=122756253 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

yeah I know if we create an index for the created_at column means the problem will solve does this have any other way to optimize?

Answer 1

Well, you could delete all the data, then the query would be very quick (just kidding)!

Honestly, you need to create the index to optimize this query. The best index for this query would be a compound index on (service_id, transaction_date, created_at) in that order.

There are at least three ways to create the index without much downtime:

• pt-online-schema-change
• gh-ost
• Create the index on a replica database, then when the index is ready, switch the app to use the replica

At my last job we used pt-online-schema-change. While I worked there we executed over 75,000 ALTER TABLEs in production during peak hours, without downtime. Some of the tables were larger than yours.