The base table and the tracing table use different character set for the primary key, which greatly reduces the query performance

[wzd24]

The base table and the tracing table use different character sets for the primary key, which greatly reduces the query performance.
eg:
The base table DDL:

CREATE TABLE `ezreportdevicecheckresult` (
  `Id` char(36) CHARACTER SET ascii NOT NULL,
  `DeviceId` char(36) CHARACTER SET ascii NOT NULL,
  `ProjectId` char(36) CHARACTER SET ascii NOT NULL,
  `CheckPointId` char(36) CHARACTER SET ascii NOT NULL,
  `OptionId` char(36) CHARACTER SET ascii DEFAULT NULL,
  `Rank` int(11) DEFAULT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `Result` int(11) DEFAULT NULL,
  `BeforPhoto` char(36) CHARACTER SET ascii DEFAULT NULL,
  `AfterPhtot` char(36) CHARACTER SET ascii DEFAULT NULL,
  `Measures` varchar(255) DEFAULT NULL,
  `CreationTime` datetime(6) NOT NULL,
  `CreatorId` char(36) CHARACTER SET ascii DEFAULT NULL,
  `LastModificationTime` datetime(6) DEFAULT NULL,
  `LastModifierId` char(36) CHARACTER SET ascii DEFAULT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
  `DeleterId` char(36) CHARACTER SET ascii DEFAULT NULL,
  `DeletionTime` datetime(6) DEFAULT NULL,
  `Material` longtext,
  PRIMARY KEY (`Id`) USING BTREE,
  KEY `IX_EzReportDeviceCheckResult_CheckPointId` (`CheckPointId`) USING BTREE,
  KEY `IX_EzReportDeviceCheckResult_DeviceId` (`DeviceId`) USING BTREE,
  KEY `IX_EzReportDeviceCheckResult_OptionId` (`OptionId`) USING BTREE,
  KEY `IX_EzReportDeviceCheckResult_ProjectId` (`ProjectId`) USING BTREE,
  CONSTRAINT `FK_EzReportDeviceCheckResult_EzReportCheckPointOption_OptionId` FOREIGN KEY (`OptionId`) REFERENCES `ezreportcheckpointoption` (`Id`),
  CONSTRAINT `FK_EzReportDeviceCheckResult_EzReportCheckPoint_CheckPointId` FOREIGN KEY (`CheckPointId`) REFERENCES `ezreportcheckpoint` (`Id`) ON DELETE CASCADE,
  CONSTRAINT `FK_EzReportDeviceCheckResult_MainDataDevice_DeviceId` FOREIGN KEY (`DeviceId`) REFERENCES `maindatadevice` (`Id`) ON DELETE CASCADE,
  CONSTRAINT `FK_EzReportDeviceCheckResult_MainDataProject_ProjectId` FOREIGN KEY (`ProjectId`) REFERENCES `maindataproject` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;`

The generated tracking table DDL:

CREATE TABLE `ezreportdevicecheckresult_tracking` (
  `Id` char(36) NOT NULL,
  `update_scope_id` varchar(36) DEFAULT NULL,
  `timestamp` bigint(20) DEFAULT NULL,
  `sync_row_is_tombstone` bit(1) NOT NULL DEFAULT b'0',
  `last_change_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE,
  KEY `ezreportdevicecheckresult_tracking_timestamp` (`timestamp`) USING BTREE,
  KEY `ezreportdevicecheckresult_tracking_timestamp_scope_id` (`Id`,`timestamp`,`update_scope_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;`

The query statistics:

# Time: 2023-10-09T17:01:28.145013+08:00
# User@Host: root[root] @  [172.16.0.5]  Id: 41807664
# Query_time: 1799.823835  Lock_time: 0.000354 Rows_sent: 102525  Rows_examined: 2461440 Thread_id: 41807664 Schema: sea-prd Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 24510676 Read_first: 24 Read_last: 0 Read_key: 24 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 2461464 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2023-10-09T16:31:28.321178+08:00 End: 2023-10-09T17:01:28.145013+08:00 Launch_time: 0.000000
# QC_Hit: No  Full_scan: Yes  Full_join: Yes  Tmp_table: No  Tmp_table_on_disk: No  Filesort: No  Filesort_on_disk: No
use sea-prd;
SET timestamp=1696840288;
SELECT	`side`.`Id`, 
	`base`.`DeviceId`, 
	`base`.`ProjectId`, 
	`base`.`CheckPointId`, 
	`base`.`OptionId`, 
	`base`.`Rank`, 
	`base`.`Description`, 
	`base`.`Result`, 
	`base`.`BeforPhoto`, 
	`base`.`AfterPhtot`, 
	`base`.`Measures`, 
	`base`.`CreationTime`, 
	`base`.`CreatorId`, 
	`base`.`LastModificationTime`, 
	`base`.`LastModifierId`, 
	`base`.`IsDeleted`, 
	`base`.`DeleterId`, 
	`base`.`DeletionTime`, 
	`base`.`Material`, 
	`side`.`sync_row_is_tombstone`, 
	`side`.`update_scope_id` as `sync_update_scope_id` 
FROM `EzReportDeviceCheckResult` `base`
RIGHT JOIN `EzReportDeviceCheckResult_tracking` `side` ON `base`.`Id` = `side`.`Id`
WHERE (
	`side`.`timestamp` > 16922403351973
	AND (`side`.`update_scope_id` <> 'f55c7d2a-1fd4-4b10-a140-3749596b0e20' OR `side`.`update_scope_id` IS NULL) 
);`

sql explain:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	side	NULL	ALL	ezreportdevicecheckresult_tracking_timestamp	NULL	NULL	NULL	203300	45.50	Using where
1	SIMPLE	base	NULL	ALL	NULL	NULL	NULL	NULL	199442	100.00	Using where; Using join buffer (Block Nested Loop)

If I change character set of 'chezreportdevicecheckresult_tracking.id' to 'ascii',the sql expain is :

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	side	NULL	ALL	ezreportdevicecheckresult_tracking_timestamp	NULL	NULL	NULL	201550	45.50	Using where
1	SIMPLE	base	NULL	eq_ref	PRIMARY	PRIMARY	36	sea_prd.side.Id	1	100.00	NULL

The query time is 0.002s

[wzd24]

My database server is MySQL 5.4.

[Mimetis]

I think the tracking table is not using a specific CHARACTER SET , and is relying on the default database one

[wzd24]

Maybe we can get the Character set of the base table's primary key from information_schema to specify the Character set of the tracing table's primary key.

[Mimetis]

Yes, that could be a good idea.
Don't have enough time nowadays, but I will look into it as soon as I have some time.
If you feel it, you can also make a PR !

[wzd24]

I'd love to, and I want to create the patch based on version 0.9.8 but I can't find the tag or branch named 0.9.8, can you create one for me?