An SQL query relating to role override function is producing large combinatorial result sets in our installation of Moodle 1.7+ (2006101009). We've seen the query below return upwards of 400k rows (around 10mb of data). Due to the large result set, this query alone takes upwards of 40 seconds to complete, depending on how many contexts are associated with the user.
- Query_time: 31 Lock_time: 0 Rows_sent: 334368 Rows_examined: 1340316
SELECT rc.capability, c1.id as id1, c2.id as id2, (c1.contextlevel * 100 + c2.contextlevel) AS aggrlevel,
rc.permission AS sum
c1.id IN (54,35,83,102,107,75,172,203,69,389,223,219,257,383,291,298,512,302,366,363,378,217,316,457,456,431,362,319,361,345,58,354,385) AND
rc.contextid != 1
AND ((ra.timestart = 0 OR ra.timestart < 1168276177) AND (ra.timeend = 0 OR ra.timeend > 1168276177))
rc.capability, (c1.contextlevel * 100 + c2.contextlevel), c1.id, c2.id, rc.permission
It appears that most of these rows are discarded in a subsequent function call that determines if c1 is a parent of c2. I understand that there have been other bug reports regarding missing INDEX after upgrading vs. installing fresh. We recently upgraded from 1.5.3+ to 1.7 (on 1/8).
Some sizes for the tables used in the above query:
mdl_role_assignments: 3907 rows
mdl_role_capabilities: 31318 rows
mdl_context: 9267 rows (and growing constantly)
If I can provide additional information that might be of use, please do not hesitate to let me know.