amrigo
06-10-2009, 04:35 PM
Hi
In Mysql this query is in the slow_query_log, can this query be optimized ? the ref is NULL for the first line from the explain this is bad ? can this be optimized ?
EXPLAIN SELECT COUNT( DISTINCT (
ra.userid) ) FROM mdl_role_assignments ra JOIN mdl_user u ON u.id = ra.userid
LEFT OUTER JOIN mdl_log l ON l.userid = ra.userid
WHERE ra.contextid IN ( 293539, 167, 6, 5, 1 )
AND ra.roleid =5 AND (l.id IS NULL
OR (l.cmid =146673 AND l.time >1242183600
AND l.action IN ('view', 'view all')));
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ra range mdl_roleassi_conroluse_uix,mdl_roleassi_rol_ix,mdl... mdl_roleassi_conroluse_uix 16 NULL 4 Using where; Using index
1 SIMPLE u eq_ref PRIMARY PRIMARY 8 moodletst.ra.userid 1 Using index
1 SIMPLE l ref mdl_log_usecou_ix mdl_log_usecou_ix 8 moodletst.u.id 448 Using where
role_assignments
id bigint(10) UNSIGNED NOT NULL auto_increment
roleid bigint(10) UNSIGNED NOT NULL 0
contextid bigint(10) UNSIGNED NOT NULL 0
userid bigint(10) UNSIGNED NOT NULL 0
hidden tinyint(1) UNSIGNED NOT NULL 0
timestart bigint(10) UNSIGNED NOT NULL 0
timeend bigint(10) UNSIGNED NOT NULL 0
timemodified bigint(10) UNSIGNED NOT NULL 0
modifierid bigint(10) UNSIGNED NOT NULL 0
enrol varchar(20) utf8_general
sortorder bigint(10) UNSIGNED NOT NULL 0
In Mysql this query is in the slow_query_log, can this query be optimized ? the ref is NULL for the first line from the explain this is bad ? can this be optimized ?
EXPLAIN SELECT COUNT( DISTINCT (
ra.userid) ) FROM mdl_role_assignments ra JOIN mdl_user u ON u.id = ra.userid
LEFT OUTER JOIN mdl_log l ON l.userid = ra.userid
WHERE ra.contextid IN ( 293539, 167, 6, 5, 1 )
AND ra.roleid =5 AND (l.id IS NULL
OR (l.cmid =146673 AND l.time >1242183600
AND l.action IN ('view', 'view all')));
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ra range mdl_roleassi_conroluse_uix,mdl_roleassi_rol_ix,mdl... mdl_roleassi_conroluse_uix 16 NULL 4 Using where; Using index
1 SIMPLE u eq_ref PRIMARY PRIMARY 8 moodletst.ra.userid 1 Using index
1 SIMPLE l ref mdl_log_usecou_ix mdl_log_usecou_ix 8 moodletst.u.id 448 Using where
role_assignments
id bigint(10) UNSIGNED NOT NULL auto_increment
roleid bigint(10) UNSIGNED NOT NULL 0
contextid bigint(10) UNSIGNED NOT NULL 0
userid bigint(10) UNSIGNED NOT NULL 0
hidden tinyint(1) UNSIGNED NOT NULL 0
timestart bigint(10) UNSIGNED NOT NULL 0
timeend bigint(10) UNSIGNED NOT NULL 0
timemodified bigint(10) UNSIGNED NOT NULL 0
modifierid bigint(10) UNSIGNED NOT NULL 0
enrol varchar(20) utf8_general
sortorder bigint(10) UNSIGNED NOT NULL 0