how to rewrite a query for the latin1 and utf8mb4

Question

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 |

This question would be better asked over on the DBA site where there is already a potential answer for you https://dba.stackexchange.com/questions/123572/convert-mysql-database-from-latin1-to-utf8mb4-and-take-care-of-german-umlauts So you could just read the answer and then delete this question as they also dont want duplicates — RiggsFolly, Oct 12 '22 at 07:44
Of course, dont make any serious changes without doing a backup of your database first, in case you need a second try! — RiggsFolly, Oct 12 '22 at 07:46
but i want to rewrite the query also ... in the( join load ) by using subquery and covert the charset. — RRQ, Oct 12 '22 at 07:46
ok ok, if it did not make any impact means it's nice .. and will sure do a backup while making changes. — RRQ, Oct 12 '22 at 07:50
Ok, so now we are into the 1 question per question issue. So ask that as a seperate question — RiggsFolly, Oct 12 '22 at 07:52
i was referring to the issue to solve the query need cuz it doesn't use an index for the loan table due to the charset changes .. so can you help me to rewrite the above query? — RRQ, Oct 12 '22 at 07:55
You have asked several questions here. That is fine, it's why we are here. BUT: they sometimes lack necessary information. To help you with your [tag:query-optimization] questions, we need to see your *entire* table definitions, your index definitions, and the output of EXPLAIN. In MySQL you can get table and index definitions with `SHOW CREATE TABLE tablename;` You should include that in your questions. Please [read this](https://stackoverflow.com/tags/query-optimization/info).. — O. Jones, Oct 12 '22 at 14:01

O. Jones · Accepted Answer · 2022-10-12T14:04:20.510

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.

how to rewrite a query for the latin1 and utf8mb4

1 Answers1