How to change the charset in a mysql query?

Question

I have a query that doesn't pick up the index even though it was indexed. When I didn't join the tables, it picked up the index, but when I joined two tables, it didn't pick up the index, which made me wonder why it went for the ref index when the other table was already using the index. Can we get rid of the full table scan for this?

Query;

select 
  distinct be.bike_name, 
  min(event_time) event_time, 
  event_type, 
  event_reported_by 
from 
  bike_info bi, 
  bike_events be 
where 
  be.bike_name = bi.bike_name 
  and be.event_time >= bi.is_active_change_dt 
  and event_type in (
    'BIKE_FAULT', 'BIKE_LOCK_FAULT', 
    'BIKE_RELOCATING', 'BIKE_DAMAGED', 
    'BIKE_UNAVAILABLE', 'BIKE_MAINTENANCE'
  ) 
  and event_value = 1 
group by 
  be.bike_name, 
  event_type, 
  event_reported_by;

Explain plan;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: be
   partitions: NULL
         type: ALL
possible_keys: bike_event_idx2,bike_event_idx1,bike_event_idx3,bike_event_idx4
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 79495612
     filtered: 8.28
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: bi
   partitions: NULL
         type: eq_ref
possible_keys: bike_name_UNIQUE,bike_info_idx6
          key: bike_name_UNIQUE
      key_len: 38
          ref: yulu_1.be.bike_name
         rows: 1
     filtered: 33.33
        Extra: Using index condition; Using where

Table structure;

*************************** 1. row ***************************
       Table: bike_events
Create Table: CREATE TABLE `bike_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `bike_name` varchar(12) NOT NULL DEFAULT '0',
  `event_type_id` int NOT NULL,
  `event_type` varchar(45) NOT NULL,
  `event_value` tinyint(1) DEFAULT '0',
  `event_comments` varchar(45) DEFAULT NULL,
  `event_time` int NOT NULL,
  `event_reported_by` int DEFAULT NULL,
  `source_id` tinyint(1) DEFAULT NULL,
  `source_cd` varchar(45) DEFAULT NULL,
  `whs_id` int DEFAULT NULL,
  `latitude` double(20,18) DEFAULT NULL,
  `longitude` double(20,18) DEFAULT NULL,
  `created_by` bigint unsigned NOT NULL DEFAULT '0',
  `created_dt` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `bike_event_idx2` (`event_type`),
  KEY `bike_event_idx1` (`bike_name`,`event_type`),
  KEY `bike_event_idx3` (`bike_name`,`event_type`,`event_time`),
  KEY `bike_event_idx4` (`bike_name`),
  KEY `bike_event_idx5` (`event_type_id`,`event_time`),
  KEY `idx_created_dt` (`created_dt`)
) ENGINE=InnoDB AUTO_INCREMENT=79702850 DEFAULT CHARSET=latin1


*************************** 1. row ***************************
       Table: bike_info
Create Table: CREATE TABLE `bike_info` (
  `bike_id` bigint unsigned NOT NULL,
  `bike_name` varchar(12) NOT NULL DEFAULT '0',
  `imei` varchar(20) DEFAULT NULL,
  `loc_country_cd` varchar(45) DEFAULT NULL,
  `fleet_city_id` int NOT NULL DEFAULT '0',
  `fleet_id` smallint unsigned NOT NULL DEFAULT '1',
  `loc_city_cd` varchar(45) DEFAULT NULL,
  `intraCampus_Flag` varchar(1) DEFAULT 'N',
  `campus_name` varchar(45) DEFAULT NULL,
  `notes` varchar(50) DEFAULT NULL,
  `manu_date` int DEFAULT NULL,
  `invoice_number` int DEFAULT NULL,
  `invoice_date` int DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `is_active_change_dt` int DEFAULT NULL,
  `is_active_change_by` int DEFAULT NULL,
  `is_assembled` int DEFAULT '0',
  `assembled_dt` int DEFAULT NULL,
  `assembled_by` int DEFAULT NULL,
  `ready_to_deploy` int DEFAULT '0',
  `ready_to_deploy_dt` int DEFAULT NULL,
  `deployed_status` int NOT NULL,
  `deployed_date` int DEFAULT NULL,
  `deployed_dt_YYYYMMDD` int DEFAULT NULL,
  `approval_dt` int DEFAULT NULL,
  `approved_by` int DEFAULT NULL,
  `retired_dt` int unsigned DEFAULT NULL,
  `retired_by` bigint unsigned DEFAULT '0',
  `retired_reason` varchar(100) DEFAULT NULL,
  `corporate_flag` int DEFAULT '0',
  `stolen_flag` int DEFAULT '0',
  `flag_sec_battery` int NOT NULL DEFAULT '0',
  `sec_battery_last_flagged_dt` int NOT NULL DEFAULT '0',
  `sec_battery_report_count` int NOT NULL DEFAULT '0',
  `firmware_version` varchar(20) DEFAULT NULL,
  `firmware_updated_dt` int unsigned DEFAULT NULL,
  `warehouse_tagged` int DEFAULT '0',
  `created_by` bigint unsigned DEFAULT NULL,
  `created_dt` int DEFAULT NULL,
  `updated_by` bigint unsigned DEFAULT NULL,
  `updated_dt` int DEFAULT NULL,
  PRIMARY KEY (`bike_id`),
  UNIQUE KEY `bike_name_UNIQUE` (`bike_name`),
  UNIQUE KEY `bike_info_ux2` (`imei`),
  KEY `bike_info_idx1` (`deployed_status`,`loc_city_cd`,`bike_name`),
  KEY `bike_info_idx2` (`loc_city_cd`),
  KEY `bike_info_idx3` (`loc_city_cd`,`deployed_status`,`campus_name`,`bike_name`),
  KEY `bike_info_idx5` (`fleet_id`),
  KEY `bike_info_idx6` (`bike_name`,`fleet_city_id`,`corporate_flag`),
  KEY `bike_info_idx7` (`updated_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

Is there an issue with the charset? because the charset was different. If it was, please tell me how to change and modify it.

Charsets differs. So your `be.bike_name = bi.bike_name` needs in charset convertion. Index cannot be used in this case. — Akina, Jan 16 '23 at 05:59
could you please describe how to covert the charset by query — RRQ, Jan 16 '23 at 06:20
Do you want to change the charset in one of the tables for all data or convert it each time you query it? For the first aproach see https://stackoverflow.com/a/8906937/3604523 — h.m.i.13, Jan 17 '23 at 08:45
I have changed the query like ```be.bikename=covert(bi.bikename using latin1)``` the row scans reduced but performance has not yet improved @h.m.i.13 — RRQ, Jan 17 '23 at 09:26
Is altering the table to charset latin1 (or the other to utf8) no solution? — h.m.i.13, Jan 17 '23 at 10:33

How to change the charset in a mysql query?

0 Answers0