jethro-pmm
jethro-pmm copied to clipboard
tbar0970
Slow query
This one shouldn't be taking so long. Check indexes etc.
# Query_time: 1.292153 Lock_time: 0.000300 Rows_sent: 247 Rows_examined: 226431
SET timestamp=1674608775;
SELECT person_group.id, person_group.name, person_group.categoryid, person_group.is_archived, person_group.owner, person_group.show_add_family, person_group.share_member_details, person_group.attendance_recording_days, COUNT(aperson.id) as member_count, pgc.name as category
FROM person_group
LEFT JOIN person_group_membership gm ON gm.groupid = person_group.id
LEFT JOIN person aperson ON gm.personid = aperson.id AND aperson.status<>'archived'
LEFT JOIN person_group_category pgc ON person_group.categoryid = pgc.id
WHERE (person_group.is_archived = '0')
GROUP BY person_group.id
ORDER BY person_group.`name`;
mysql> explain SELECT person_group.id, person_group.name, person_group.categoryid, person_group.is_archived, person_group.owner, person_group.show_add_family, person_group.share_member_details, person_group.attendance_recording_days, COUNT(aperson.id) as member_count, pgc.name as category
-> FROM person_group
-> LEFT JOIN person_group_membership gm ON gm.groupid = person_group.id
-> LEFT JOIN person aperson ON gm.personid = aperson.id AND aperson.status<>'archived'
-> LEFT JOIN person_group_category pgc ON person_group.categoryid = pgc.id
-> WHERE (person_group.is_archived = '0')
-> GROUP BY person_group.id
-> ORDER BY person_group.`name`;
+----+--------------------+-------+----------------+------------------------------------------+------------------------------------------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+------------------------------------------+------------------------------------------+---------+----------------------+------+----------------------------------------------+
| 1 | PRIMARY | g | index | NULL | PRIMARY | 4 | NULL | 737 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | gm | ref | groupid | groupid | 4 | je_asne.g.id | 11 | Using index |
| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 4 | je_asne.gm.personid | 1 | |
| 1 | PRIMARY | pgc | eq_ref | PRIMARY | PRIMARY | 4 | je_asne.g.categoryid | 1 | Using where |
| 6 | SUBQUERY | cr | index | NULL | account_group_restriction_congregationid | 4 | NULL | 1 | Using where; Using index |
| 7 | DEPENDENT SUBQUERY | cr | index | NULL | account_group_restriction_groupid | 4 | NULL | 20 | Using where; Using index |
| 8 | DEPENDENT SUBQUERY | cr | index_subquery | account_group_restriction_congregationid | account_group_restriction_congregationid | 4 | func | 1 | Using index; Using where |
| 9 | DEPENDENT SUBQUERY | m | ref | PRIMARY,personid,groupid | personid | 4 | func | 3 | Using index |
| 9 | DEPENDENT SUBQUERY | gr | ref | account_group_restriction_groupid | account_group_restriction_groupid | 4 | je_asne.m.groupid | 1 | Using where; Using index |
| 3 | SUBQUERY | gr | index | NULL | account_group_restriction_groupid | 4 | NULL | 20 | Using where; Using index |
| 4 | DEPENDENT SUBQUERY | gr | index_subquery | account_group_restriction_groupid | account_group_restriction_groupid | 4 | func | 1 | Using index; Using where |
+----+--------------------+-------+----------------+------------------------------------------+------------------------------------------+---------+----------------------+------+----------------------------------------------+
11 rows in set (0.04 sec)
I think the contiributing factors are a) the fact that person_group is a view, with the various restriction tables behind it b) the fact that we're joining onto the person view, which also has various restructions c) we're joining all group members, but only because we want to count non-archived members.
Idea: We could join onto the _person table instead of the person view. Visibility restrictions would not be enforced, but since this query groups by person_group.id I think we can be confident it won't ever be returning any details of individual persons.
FYI, Mariadb 10.6 does that query on that database in 0.069s. The query plan is different:
+------+--------------------+-------+--------+-----------------------------------------------+---------+---------+----------------------+------+----------------------------------------------+
| 1 | PRIMARY | g | index | NULL | PRIMARY | 4 | NULL | 710 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | gm | ref | groupid | groupid | 4 | je_asne.g.id | 10 | Using index |
| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 4 | je_asne.gm.personid | 1 | Using where |
| 1 | PRIMARY | pgc | eq_ref | PRIMARY | PRIMARY | 4 | je_asne.g.categoryid | 1 | Using where |
| 9 | MATERIALIZED | gr | index | PRIMARY,account_group_restriction_groupid | PRIMARY | 8 | NULL | 20 | Using where; Using index |
| 9 | MATERIALIZED | m | ref | PRIMARY,personid,groupid,membership_status_fk | groupid | 4 | je_asne.gr.groupid | 10 | Using index |
| 8 | MATERIALIZED | cr | index | account_group_restriction_congregationid | PRIMARY | 8 | NULL | 1 | Using where; Using index |
| 7 | DEPENDENT SUBQUERY | cr | index | NULL | PRIMARY | 8 | NULL | 20 | Using where; Using index |
| 6 | SUBQUERY | cr | index | NULL | PRIMARY | 8 | NULL | 1 | Using where; Using index |
| 4 | MATERIALIZED | gr | index | account_group_restriction_groupid | PRIMARY | 8 | NULL | 20 | Using where; Using index |
| 3 | SUBQUERY | gr | index | NULL | PRIMARY | 8 | NULL | 20 | Using where; Using index |
+------+--------------------+-------+--------+-----------------------------------------------+---------+---------+----------------------+------+----------------------------------------------+
11 rows in set (0.006 sec)
It also seems like it's the combination of
GROUP BY person_group.id ORDER BY person_group.name
which is triggering the temporary table/filesort.
https://stackoverflow.com/questions/13633406/using-index-using-temporary-using-filesort-how-to-fix-this
Removing the ORDER BY takes away the temp-table/filesort from the query plan
Moving the member-counting into a subquery, so the outer query doesn't need a GROUP BY, didn't help as much as I'd hoped. MariaDB probably the best solution. Also, the member-count is often not needed, so the application code could be reworked to only add it to the query when necessary.