MAINTENACE_cleanLogs doesn't complete on MySQL server

MAINTENANCE_cleanLogs with > 2M rows wasn't completing in less than 300 sec and MySQL server was killing the query provided by Shilen in TIER grouper chat room. Chat room follows:

Michael Gettes [21:47]
@Shilen Patel i couldn’t get the cleanLogs job to run (see https://internet2.slack.com/archives/C7V0UQDJ4/p1553515725317200) in less than 300 seconds; mySQL would keep killing it. after staring at the SQL for a bit, i realized grouper_attribute_assign.attribute_assign_type in the subquery wasn’t indexed. I indexed it and it ran in under a second - the cleanLogs job now runs completely.
Shilen Patel
This is the query, does it work for you? SELECT distinct gaa., gadn., gad.* FROM grouper_attribute_assign gaa, grouper_attribute_def_name gadn, grouper_attribute_def gad
WHERE gaa.attribute_def_name_id = gadn.id AND gadn.attribute_def_id = gad.id AND gad.multi_assignable = 'F'
AND EXISTS (SELECT 1 FROM grouper_attribute_assign gaa2 WHERE gaa2.id != gaa.id AND gaa2.attribute_assign_action_id = gaa.attribute_assign_action_id
AND gaa2.attribute_def_name_id = gaa.attribute_def_name_id AND gaa2.attribute_assign_type = gaa.attribute_assign_type
AND gaa2.attribute_assign_type = 'stem' AND gaa2.owner_stem_id = gaa.owner_stem_id)
Posted in #tier-grouperYesterday at 08:08View message