The flagging table for Drupal 9 had entity_id field as Varchar which prevented indexing to work during joins as entity_ids from other tables were unsigned int.
Flagging table before:
flagging | CREATE TABLE `flagging` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`flag_id` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
`uuid` varchar(128) CHARACTER SET ascii NOT NULL,
`entity_type` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`entity_id` int unsigned DEFAULT NULL,
`global` tinyint DEFAULT NULL,
`uid` int unsigned NOT NULL COMMENT 'The ID of the target entity.',
`session_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`created` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `flagging_fid_etid` (`flag_id`,`entity_id`),
KEY `flagging_fid_uid_etid` (`flag_id`,`uid`,`entity_id`),
KEY `flagging_type_fid_etid` (`entity_type`,`flag_id`,`entity_id`),
KEY `flagging_type_fid_uid_etid` (`entity_type`,`flag_id`,`uid`,`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2135664 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='The base table for flagging entities.'MySQL alter table to convert entity_id to unsigned int:
ALTER TABLE flagging MODIFY entity_id INT UNSIGNED DEFAULT NULL;Source:
--
https://dba.stackexchange.com/questions/316226/join-with-certain-tables-...
https://stackoverflow.com/questions/21784201/mysql-convert-column-dataty...
Technologies: