mysql - Query Performance Optimization -

-

i have mysql query :

select tbl.userid, tbl.name, tbl.courseid, tbl.fullname, ifnull(tbl.instance, '-') instance, ifnull(tbl.activityname, '-') activityname,     ifnull(tbl.module, '-') module, ifnull(tbl.attempt, '-') attempt, ifnull(tbl.score, '-') score,     case tbl.module when 'quiz' (         case when (tbl.attempt null , tbl.score null) 'not yet'         when (tbl.attempt not null , tbl.score null) 'attempt'         when (tbl.attempt not null , tbl.score not null) 'finish'         end)     when 'scorm' (         case when tbl.attempt null 'not yet'         else (select `status` mdl_scorm_logs scormid = tbl.instance , userid = tbl.userid)         end)     else '-'     end `status` (     select tbl1.userid, tbl1.name, tbl1.courseid, tbl1.fullname, tbl2.instance,         case tbl2.module when 'quiz' (select `name` mdl_quiz id = tbl2.instance , course = tbl1.courseid)         when 'scorm' (select `name` mdl_scorm id = tbl2.instance , course = tbl1.courseid)         end activityname         , tbl2.module,          case tbl2.module when 'quiz' (select max(attempt) mdl_quiz_attempts quiz = tbl2.instance , userid = tbl1.userid)         when 'scorm' (select max(attempt) mdl_scorm_scoes_track scormid = tbl2.instance , userid = tbl1.userid )         end attempt,         (select concat(truncate(round(gg.finalgrade / gi.grademax * 100, 2), 2), ' %')         mdl_grade_grades gg         join mdl_grade_items gi on gg.itemid = gi.id         gg.userid = tbl1.userid , gi.courseid = tbl1.courseid , gi.itemname = activityname , gi.itemtype = 'mod'             , gi.itemmodule = tbl2.module , gi.iteminstance = tbl2.instance) score     (         select u.id userid, concat(u.firstname, ' ', u.lastname) `name`, c.id courseid, c.fullname         mdl_user u         join mdl_role_assignments ra on u.id = ra.userid         join mdl_context co on ra.contextid = co.id         join mdl_role r on ra.roleid = r.id         join mdl_course c on co.instanceid = c.id         join mdl_enrol e on c.id = e.courseid         join mdl_user_enrolments ue on e.id = ue.enrolid , u.id = ue.userid         r.archetype = 'student') tbl1     left join (         select cm.course, m.name module, cm.instance mdl_course_modules cm         join mdl_modules m on cm.module = m.id         m.name in ('quiz', 'scorm')         ) tbl2     on tbl1.courseid = tbl2.course) tbl tbl.userid = '4' , tbl.courseid = '8' group tbl.courseid, tbl.instance order tbl.name, tbl.fullname, tbl.activityname

result after use explain :

id  select_type table   type    possible_keys   key key_len ref rows    1   primary <derived3>                  122 using where; using temporary; using filesort 3   derived <derived9>                  42   3   derived <derived10>                 23   10  derived m   range   primary,mdl_modu_nam_ix mdl_modu_nam_ix 62      2   using where; using index 10  derived cm  ref mdl_courmodu_mod_ix mdl_courmodu_mod_ix 8   moodle.m.id 4    9   derived r   ref primary,mdl_role_arc_ix mdl_role_arc_ix 92      1   using where; using index 9   derived ra  ref mdl_roleassi_rol_ix,mdl_roleassi_con_ix,mdl_roleassi_use_ix mdl_roleassi_rol_ix 8   moodle.r.id 5    9   derived u   eq_ref  primary primary 8   moodle.ra.userid    1    9   derived co  eq_ref  primary,mdl_cont_ins_ix primary 8   moodle.ra.contextid 1    9   derived e   ref primary,mdl_enro_cou_ix mdl_enro_cou_ix 8   moodle.co.instanceid    1   using index 9   derived c   eq_ref  primary primary 8   moodle.co.instanceid    1    9   derived ue  eq_ref  mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix mdl_userenro_enruse_uix 16  moodle.e.id,moodle.ra.userid    1   using index 8   dependent subquery  gi  ref primary,mdl_graditem_itenee_ix,mdl_graditem_cou_ix  mdl_graditem_cou_ix 9   tbl1.courseid   1   using 8   dependent subquery  gg  eq_ref  mdl_gradgrad_useite_uix,mdl_gradgrad_ite_ix,mdl_gradgrad_use_ix mdl_gradgrad_useite_uix 16  tbl1.userid,moodle.gi.id    1   using 7   dependent subquery  mdl_scorm_scoes_track   ref mdl_scorscoetrac_usescosco_uix,mdl_scorscoetrac_use_ix,mdl_scorscoetrac_sco_ix  mdl_scorscoetrac_usescosco_uix  16  tbl1.userid,tbl2.instance   6   using where; using index 6   dependent subquery  mdl_quiz_attempts   ref mdl_quizatte_use_ix,mdl_quizatte_qui_ix mdl_quizatte_qui_ix 8   tbl2.instance   2   using 5   dependent subquery  mdl_scorm   eq_ref  primary,mdl_scor_cou_ix primary 8   tbl2.instance   1   using 4   dependent subquery  mdl_quiz    eq_ref  primary,mdl_quiz_cou_ix primary 8   tbl2.instance   1   using 2   dependent subquery  mdl_scorm_logs  eq_ref  primary primary 16  func,func   1   using

any idea optimize it? thank you.

omg. denormalization way handle situation. ok, optimize query. next time, when have join 15 tables in single query?

your query looks report. should design database reporting. check out following links, may useful you.

http://en.wikipedia.org/wiki/denormalization

http://en.wikipedia.org/wiki/star_schema


Comments

Post a Comment

Popular posts from this blog

php - What is the difference between $_SERVER['PATH_INFO'] and $_SERVER['ORIG_PATH_INFO']? -

-
what’s difference between $_server['path_info'] , $_server['orig_path_info'] ? how use them? when run print_r($_server) , path_info , orig_path_info not present in array. why not? how can enable them? i have read php manual on them, still don’t understand them. the path_info variable present if invoke php script this: http://www.example.com/phpinfo.php/hello_there it's /hello_there part after .php script. if don't invoke url that, there won't $_server["path_info"] environment variable. the porig_ prefix uncommon. path_info standard cgi-environment variable, , should never prefixed. did read that? (there issues around php3/php4 if invoked php interpreter via cgi-bin/ - hardly has such setups today.) for reference: http://www.ietf.org/rfc/rfc3875
Read more

fortran - Function return type mismatch -

-
i'm attempting recode old c++ program in fortran make use of lapack (i'm aware c++ have lapack++, i'm having lot of trouble installing it, gave up). i didn't have problems compilation, when had variables declared real . when started coding section of program required lapack, found parameters passed dsyev need double precision . tried change double precision (including changing hard coded numbers double precision counterparts, i.e. 0.0 -> 0.0d0) when try compile, following error of functions , subroutines i've written: error: return type mismatch of function <function> @ (1) (real(4)/real(8)) i'm not sure coming from, in program has been changed double precision. for example, i've declared following: double precision :: alpha(3),d(3),zeta1,zeta2 double precision :: a1(3),a2(3),d1(3),d2(3) double precision :: pi pi = 3.14159265359d0 alpha = (/0.109818d0, 0.405771d0, 2.22766d0/) d = (/0.444635d0, 0.535328d0, 0.154329d0 /) 10 i=1,3 ...
Read more

queue - mq_receive: message too long -

-
i implementing communication between 2 processes using queue. problem when call function mq_receive, error: message long. i have done following: struct mq_attr attr; long size = attr.mq_msgsize; .... // initializing queue "/gateway" int rc = mq_receive(gateway, buffer, size, &prio); if print size value, size=1, while when print same size program (got same mechanism), not long integer ( -1217186280 )... how can solve error?....so while size = 1, believe it's right "message long" why 1? p.s. have tried put : int rc = mq_receive(gateway, buffer, sizeof(buffer), &prio); but no result. it seems need read docs more carefully. when call mq_receive should pass size of destination buffer. this size must greater mq_msgsize attribute of queue . in addition, seems have error in queue attributes initialisation makes proper mq_receive call impossible. here standard message queue session: fill mq_attr struct ( doc ): struct mq_a...
Read more