how to rewrite a query for the latin1 and utf8mb4-CodePudding

the below 2 tables contain different charset can I change the charset does it impact any data or is it safer? and for now, I want to change the query by using the (covert using latin1 )function but I'm not sure how to possibly change the query.

Question 1: does anything will happen while changing the charset ?

Question 2 : did the below query have any possible rewrites?

select 
  adc.consent_id, 
  adc.user_id, 
  adc.loan_id, 
  ls.loan_schedule_id, 
  adc.max_amount, 
  adc.method, 
  ls.due_date, 
  DATEDIFF(
    CURDATE(), 
    ls.due_date
  ), 
  l.status_code 
from 
  razorpay_enach_consent as adc 
  join loan_schedules as ls on adc.loan_id = ls.loan_id 
  AND adc.is_active = 1 
  AND adc.token_status = 'confirmed' 
  and ls.due_date <= DATE_ADD(now(), INTERVAL 1 DAY) 
  and ls.status_code = 'repayment_pending' 
  and due_date >= date_sub(now(), INTERVAL 2 day) 
  and ls.loan_schedule_id not in (
    select 
      loan_schedule_id 
    from 
      repayment_transactions rt 
    where 
      rt.status_code in (
        'repayment_auto_debit_order_created', 
        'repayment_auto_debit_request_sent', 
        'repayment_transaction_inprogress'
      ) 
      and rt.entry_type = 'AUTODEBIT_RP'
  ) 
  join loans l on adc.loan_id = l.loan_id 
  and l.status_code = 'disbursal_completed' 
limit 
  30

explain plan

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: adc
   partitions: NULL
         type: ref
possible_keys: idx_is_active_loan_id
          key: idx_is_active_loan_id
      key_len: 1
          ref: const
         rows: 829
     filtered: 10.00
        Extra: Using index condition; Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: l
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_loans_status_code,idx_lid_uid_bnk_statcd,idx_loan_id_tenure_days,idx_disbursal_date_status_code
          key: PRIMARY
      key_len: 8
          ref: loanfront.adc.loan_id
         rows: 1
     filtered: 7.15
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: ls
   partitions: NULL
         type: ref
possible_keys: fk_loan_schedules_loans
          key: fk_loan_schedules_loans
      key_len: 8
          ref: loanfront.adc.loan_id
         rows: 1
     filtered: 4.09
        Extra: Using index condition; Using where
*************************** 4. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: rt
   partitions: NULL
         type: index_subquery
possible_keys: idx_transactions_status_code,idx_repayment_transactions,idx_entry_type_status_code
          key: idx_repayment_transactions
      key_len: 5
          ref: func
         rows: 4
     filtered: 1.10
        Extra: Using where

Table structure ;

Table : loan_schedules
(`tmp_user_id`,`tmp_loan_id`,`emi_group`,`schedule_num`),
  KEY `fk_loan_schedules_product_types` (`product_type_id`),
  KEY `fk_loan_schedules_loans` (`loan_id`),
  KEY `loan_schedules_tmp_user_loan_group_schedule_num` (`tmp_user_id`,`tmp_loan_id`,`emi_group`,`schedule_num`),
  KEY `loan_schedules_emi_group_index` (`emi_group`),
  KEY `loan_schedules_tmp_loan_id_index` (`tmp_loan_id`),
  KEY `loan_schedules_tmp_user_id_index` (`tmp_user_id`),
  KEY `loan_schedules_user_id_index` (`user_id`),
  KEY `idx_schedule_num_expected_total_am_status_code` (`schedule_num`,`expected_total_amt`,`status_code`),
  CONSTRAINT `_fk_loan_schedules_product_types` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`product_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8622683 DEFAULT CHARSET=latin1 |


table: razorpay_enach_consent

payment_created_at` timestamp NULL DEFAULT NULL,
  `token_id` varchar(200) DEFAULT NULL,
  `token_expiry_date` timestamp NULL DEFAULT NULL,
  `signature` varchar(500) DEFAULT NULL,
  `is_active` tinyint(2) NOT NULL DEFAULT '1',
  PRIMARY KEY (`consent_id`),
  UNIQUE KEY `token_id_UNIQUE` (`token_id`),
  KEY `idx_is_active_loan_id` (`is_active`,`loan_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4989 DEFAULT CHARSET=utf8mb4 |

CodePudding user response：

The consequences of changing a collation or character set on a column in a table, or on the table as a whole, are:

converting the former character set / collation values to the new one. That's not always possible: For example the unicode ⌘ cannot be represented in latin1. See this.
rebuilding any indexes containing textual columns.

If you use text (VARCHAR, CHAR, TEXT, MEDIUMTEXT and so forth) data types in ON conditions for JOINs, the character sets and collations of the columns should match. If you use numerical data types (INT, BIGINT) those data types should match. If they don't MySQL or MariaDB must do a lot of on-the-fly conversion to evaluate the ON condition.

Collations and character sets are baked into indexes. So if you do this on your utfmb4 column razorpay_enach_consent.token_id

WHERE CONVERT(token_id USING latin1) = _latin1'Constant

you'll defeat an index on token_id. But if you do this, you won't.

WHERE token_id = _utf8mb4'Constant

you'll use the index.