SQLize | PHPize | SQLtest

CREATE TABLE `SyncopeUser` ( `id` varchar(36) NOT NULL, `creationContext` varchar(255) DEFAULT NULL, `creationDate` datetime(3) DEFAULT NULL, `creator` varchar(255) DEFAULT NULL, `lastChangeContext` varchar(255) DEFAULT NULL, `lastChangeDate` datetime(3) DEFAULT NULL, `lastModifier` varchar(255) DEFAULT NULL, `status` varchar(255) DEFAULT NULL, `changePwdDate` datetime(3) DEFAULT NULL, `cipherAlgorithm` varchar(20) DEFAULT NULL, `failedLogins` int(11) DEFAULT NULL, `lastLoginDate` datetime(3) DEFAULT NULL, `mustChangePassword` int(11) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `passwordHistory` text DEFAULT NULL, `securityAnswer` varchar(255) DEFAULT NULL, `suspended` int(11) DEFAULT NULL, `token` text DEFAULT NULL, `tokenExpireTime` datetime(3) DEFAULT NULL, `username` varchar(255) DEFAULT NULL, `plainAttrs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`plainAttrs`)), `REALM_ID` varchar(36) DEFAULT NULL, `SECURITYQUESTION_ID` varchar(36) DEFAULT NULL, PRIMARY KEY (`id`)); We a running an instance of Apache syncope with around 130k users (ca. 33k with active or pending state). When searching for users by attributes we see long query times often over 50 seconds. One example query might be select * from user_search_attr where schema_id = 'familyName' and stringvalue = 'Bergmann' user_search_attr is a view defined as follows: CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `user_search_attr` AS select `u`.`id` AS `any_id`, `attrs`.`schema_id` AS `schema_id`, `attrs`.`booleanvalue` AS `booleanvalue`, `attrs`.`datevalue` AS `datevalue`, `attrs`.`doublevalue` AS `doublevalue`, `attrs`.`longvalue` AS `longvalue`, `attrs`.`stringvalue` AS `stringvalue` from (`SyncopeUser` `u` left join JSON_TABLE(coalesce(`u`.`plainAttrs`, '[]'), '$[*]' COLUMNS (`schema_id` varchar(255) PATH '$.schema', NESTED PATH '$.values[*]' COLUMNS (`booleanvalue` int(11) PATH '$.booleanValue', `datevalue` varchar(32) PATH '$.dateValue', `doublevalue` double PATH '$.doubleValue', `longvalue` bigint(20) PATH '$.longValue', `stringvalue` varchar(255) PATH '$.stringValue'))) `attrs` on (1 = 1)) where `attrs`.`schema_id` is not null and (`attrs`.`booleanvalue` is not null or `attrs`.`datevalue` is not null or `attrs`.`doublevalue` is not null or `attrs`.`longvalue` is not null or `attrs`.`stringvalue` is not null); analyze format=json select * from user_search_attr where schema_id = 'familyName' and stringvalue = 'Bergmann';