Mysql query is taking so much time when trying to fetch data based on institutes using subquery

Sachin
November 19, 2022
275 views
0 votes
2 Answers

I have a question listing page where I want to sort the data with the following for each question

Requirement 1

Total Question attempted
Total Passed students
Passed students percentage

Requirement 2

Total Question attempted (for particular institute, Institute id is 20 in our case which we get by logged in user’s Institute)
Total Passed students (for particular institute, Institute id is 20 in our case which we get by logged in user’s Institute)
Passed students percentage (for particular institute, Institute id is 20 in our case which we get by logged in user’s Institute)

I have achieved "Requirement 1" but for the "Requirement 2" which is very similar to "Requirement 1" I forced to run 4 subqueries to find out below:

total_students_facility
passed_students_facility
percentage_facility

Which is making the query very slow for 9M records. I wonder if I can achieve "Requirement 2" also with join and correct indexing.

Also I run two subquery again to calculate percentage as we cannot use ALIAS in calculation it shows unknown column. As we can just use ALIAS in "group by", "having", and "order by" any solution will be helpful.


CREATE TABLE IF NOT EXISTS `mdl_question` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category` bigint(20) NOT NULL DEFAULT 0,
  `parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `name` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `questiontext` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `questiontextformat` tinyint(4) NOT NULL DEFAULT 0,
  `generalfeedback` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `generalfeedbackformat` tinyint(4) NOT NULL DEFAULT 0,
  `defaultmark` decimal(12,7) NOT NULL DEFAULT 1.0000000,
  `penalty` decimal(12,7) NOT NULL DEFAULT 0.3333333,
  `qtype` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '''1''',
  `length` bigint(20) UNSIGNED NOT NULL DEFAULT 1,
  `stamp` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `version` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `hidden` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `timecreated` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `timemodified` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `createdby` bigint(20) UNSIGNED DEFAULT NULL,
  `modifiedby` bigint(20) UNSIGNED DEFAULT NULL,
  `type_data_id` bigint(20) NOT NULL,
  `img_id` bigint(20) DEFAULT NULL,
  `qimg_gallary_text` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qrimg_gallary_text` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qimg_gallary_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qrimg_gallary_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `case_id` bigint(20) NOT NULL DEFAULT 0,
  `ques_type_id` bigint(20) DEFAULT NULL,
  `year` bigint(20) DEFAULT NULL,
  `spec` bigint(20) DEFAULT NULL,
  `sub_speciality_id` int(11) DEFAULT NULL,
  `sub_sub_speciality_id` int(11) DEFAULT NULL,
  `spec_level` bigint(20) DEFAULT 1,
  `is_deleted` int(11) NOT NULL DEFAULT 0,
  `sequence` int(11) NOT NULL DEFAULT 0,
  `sort_order` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Question order in list',
  `idnumber` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `addendum` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text_for_search` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'this is for the text based searching, this will store the text of the question without html tags',
  `text_for_search_ans` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `type_data_id` (`type_data_id`),
  UNIQUE KEY `mdl_ques_catidn_uix` (`category`,`idnumber`),
  KEY `mdl_ques_cat_ix` (`category`),
  KEY `mdl_ques_par_ix` (`parent`),
  KEY `mdl_ques_cre_ix` (`createdby`),
  KEY `mdl_ques_mod_ix` (`modifiedby`),
  KEY `id` (`id`),
  KEY `mq_spec_ix` (`spec`),
  KEY `sort_order` (`sort_order`),
  KEY `idx2` (`is_deleted`,`sort_order`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='The questions themselves';



CREATE TABLE IF NOT EXISTS `mdl_question_attempts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `questionusageid` bigint(20) UNSIGNED NOT NULL,
  `slot` bigint(20) UNSIGNED NOT NULL,
  `behaviour` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `questionid` bigint(20) UNSIGNED NOT NULL,
  `variant` bigint(20) UNSIGNED NOT NULL DEFAULT 1,
  `maxmark` decimal(12,7) NOT NULL,
  `minfraction` decimal(12,7) NOT NULL,
  `flagged` tinyint(3) UNSIGNED NOT NULL DEFAULT 2,
  `questionsummary` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rightanswer` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responsesummary` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timemodified` bigint(20) UNSIGNED NOT NULL,
  `maxfraction` decimal(12,7) DEFAULT 1.0000000,
  `in_remind_state` int(11) NOT NULL DEFAULT 0,
  `is_correct` tinyint(1) DEFAULT 1,
  `institution_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mdl_quesatte_queslo_uix` (`questionusageid`,`slot`),
  KEY `mdl_quesatte_que2_ix` (`questionusageid`),
  KEY `is_correct` (`is_correct`),
  KEY `idx1` (`questionid`,`is_correct`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Each row here corresponds to an attempt at one question, as ';
COMMIT;


CREATE TABLE IF NOT EXISTS `mdl_quiz_attempts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uniqueid` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `quiz` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `userid` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `attempt` mediumint(9) NOT NULL DEFAULT 0,
  `sumgrades` decimal(10,5) DEFAULT NULL,
  `timestart` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `timefinish` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `timemodified` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `layout` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `preview` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `needsupgradetonewqe` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `comments` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_deleted` int(11) NOT NULL DEFAULT 0,
  `groupname` text CHARACTER SET utf8mb3 DEFAULT NULL,
  `pageid` int(11) DEFAULT 0,
  `currentpage` bigint(20) DEFAULT 0,
  `state` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT 'inprogress',
  `timemodifiedoffline` bigint(20) NOT NULL DEFAULT 0,
  `timecheckstate` bigint(20) DEFAULT 0,
  `retake` int(11) NOT NULL DEFAULT 0,
  `is_on_going` int(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mdl_quizatte_uni_uix` (`uniqueid`),
  KEY `mdl_quizatte_use_ix` (`userid`),
  KEY `mdl_quizatte_qui_ix` (`quiz`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `mdl_user` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `auth` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manual',
  `confirmed` tinyint(1) NOT NULL DEFAULT 0,
  `policyagreed` tinyint(1) NOT NULL DEFAULT 0,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  `suspended` tinyint(1) NOT NULL DEFAULT 0,
  `mnethostid` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `idnumber` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `firstname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `lastname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `recommend_email` tinyint(1) NOT NULL DEFAULT 0,
  `emailstop` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `icq` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `skype` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `yahoo` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `aim` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `msn` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `phone1` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `phone2` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `institution` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `department` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `address` varchar(70) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `city` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `lang` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'en',
  `theme` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `timezone` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '99',
  `firstaccess` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `lastaccess` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `lastlogin` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `currentlogin` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `lastip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `secret` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `picture` tinyint(1) NOT NULL DEFAULT 0,
  `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descriptionformat` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `mailformat` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `maildigest` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `maildisplay` tinyint(3) UNSIGNED NOT NULL DEFAULT 2,
  `htmleditor` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `ajax` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `autosubscribe` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `trackforums` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `timecreated` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `timemodified` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `trustbitmask` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `imagealt` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `screenreader` tinyint(1) NOT NULL DEFAULT 0,
  `furlastname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `furfirstname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `grade` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `group_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `jobtitle` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `actstatus` enum('1','0') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `slearncount` int(11) NOT NULL DEFAULT 0,
  `agrrement_status` tinyint(1) NOT NULL DEFAULT 0,
  `excludesure` int(11) DEFAULT 0,
  `calendartype` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'gregorian',
  `lastnamephonetic` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `firstnamephonetic` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `middlename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alternatename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `version` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewertype` smallint(6) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `mdl_user_del_ix` (`deleted`),
  KEY `mdl_user_con_ix` (`confirmed`),
  KEY `mdl_user_fir_ix` (`firstname`),
  KEY `mdl_user_las_ix` (`lastname`),
  KEY `mdl_user_cit_ix` (`city`),
  KEY `mdl_user_cou_ix` (`country`),
  KEY `mdl_user_las2_ix` (`lastaccess`),
  KEY `mdl_user_ema_ix` (`email`),
  KEY `mdl_user_aut_ix` (`auth`),
  KEY `mdl_user_idn_ix` (`idnumber`),
  KEY `mdl_user_fir2_ix` (`firstnamephonetic`),
  KEY `mdl_user_las3_ix` (`lastnamephonetic`),
  KEY `mdl_user_mid_ix` (`middlename`),
  KEY `mdl_user_alt_ix` (`alternatename`),
  KEY `mdl_user_institution_ix` (`institution`),
  KEY `actstatus` (`actstatus`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='One record for each person';


CREATE TABLE IF NOT EXISTS `mdl_my_ques_type_data` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `question_id` bigint(20) DEFAULT NULL,
  `ques_pref` int(11) DEFAULT NULL,
  `ques_case` bigint(20) DEFAULT NULL,
  `ques_type_id` bigint(20) DEFAULT NULL,
  `year` bigint(20) DEFAULT NULL,
  `spec` bigint(20) DEFAULT NULL,
  `subspec` bigint(20) DEFAULT NULL,
  `subsubspec` bigint(20) DEFAULT NULL,
  `spec_level` bigint(20) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  `institute_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `question_id` (`question_id`),
  KEY `id` (`id`),
  KEY `ques_type_id` (`ques_type_id`),
  KEY `ques_case` (`ques_case`),
  KEY `spec` (`spec`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;


CREATE TABLE IF NOT EXISTS `mdl_my_ques_attributes` (
  `attrib_id` int(11) NOT NULL AUTO_INCREMENT,
  `question` bigint(20) NOT NULL,
  `related_question` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `keywords` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `explanation` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ext_link` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ext_link_txt` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `link_type` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `diff_id` int(11) DEFAULT NULL,
  `shuffle` int(11) DEFAULT NULL,
  `ownerid` int(11) DEFAULT NULL,
  `creator_id` int(11) DEFAULT NULL,
  `date_created` bigint(20) DEFAULT NULL,
  `last_modified_id` int(11) DEFAULT NULL,
  `last_modified_date` bigint(20) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `insti_id` bigint(20) NOT NULL DEFAULT 0,
  `ques_type` int(11) NOT NULL,
  `no_ques` bigint(20) NOT NULL DEFAULT 0,
  `is_deleted` tinyint(4) NOT NULL DEFAULT 0,
  `review_id` int(11) NOT NULL DEFAULT 1,
  `land_id` int(11) NOT NULL DEFAULT 1,
  `ques_label` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exclude_self_learning` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `text_for_search` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'this is for the text based searching, this will store the text of the question without html tags',
  PRIMARY KEY (`attrib_id`),
  KEY `attrib_id` (`attrib_id`),
  KEY `question` (`question`),
  KEY `diff_id` (`diff_id`),
  KEY `mqa_ques_type_ix` (`ques_type`),
  KEY `mqa_insti_id_ix` (`insti_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `specilities` (
  `sp_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `percentage` float NOT NULL DEFAULT 0,
  `color` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `order` bigint(20) NOT NULL DEFAULT 0,
  `is_deleted` int(11) NOT NULL DEFAULT 0,
  `institute_id` int(11) NOT NULL DEFAULT 0,
  `sp_level` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`sp_id`),
  KEY `sp_id` (`sp_id`),
  KEY `mq_spec_parent_ix` (`parent_id`),
  KEY `specilities_institute_id` (`institute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

select 
    mq.id, mq.ques_type_id, mq.qtype, mq.name, mq.qimg_gallary_ids, 
    qtd.question_id, qtd.year, qtd.spec, qtd.subspec, qtd.subsubspec, 
    qa.status, qa.review_id, qa.diff_id, qa.land_id, qa.insti_id, qa.exclude_self_learning, 
    COUNT(mqa.questionid) AS total_students,
    SUM(CASE WHEN is_correct = 1 then 1 else 0 end) passed_students,
    round((SUM(CASE WHEN is_correct = 1 then 1 else 0 end) / COUNT(mqa.questionid) * 100 ),2) AS percentage,
    
    (SELECT COUNT(mqas1.questionid) 
        FROM mdl_question_attempts mqas1 
        LEFT JOIN mdl_quiz_attempts mquizas1 ON mqas1.questionusageid=mquizas1.uniqueid
        LEFT JOIN mdl_user us1 ON mquizas1.userid=us1.id
        WHERE us1.institution=20 AND mqas1.questionid=mq.id) AS total_students_facility,
        
    (SELECT COUNT(mqas1.questionid) 
        FROM mdl_question_attempts mqas1 
        LEFT JOIN mdl_quiz_attempts mquizas1 ON mqas1.questionusageid=mquizas1.uniqueid
        LEFT JOIN mdl_user us1 ON mquizas1.userid=us1.id
        WHERE us1.institution=20 AND mqas1.questionid=mq.id AND mqas1.is_correct=1) AS passed_students_facility,
        
     round(((SELECT COUNT(mqas1.questionid) 
        FROM mdl_question_attempts mqas1 
        LEFT JOIN mdl_quiz_attempts mquizas1 ON mqas1.questionusageid=mquizas1.uniqueid
        LEFT JOIN mdl_user us1 ON mquizas1.userid=us1.id
        WHERE us1.institution=20 AND mqas1.questionid=mq.id AND mqas1.is_correct=1) / (SELECT COUNT(mqas1.questionid) 
        FROM mdl_question_attempts mqas1 
        LEFT JOIN mdl_quiz_attempts mquizas1 ON mqas1.questionusageid=mquizas1.uniqueid
        LEFT JOIN mdl_user us1 ON mquizas1.userid=us1.id
        WHERE us1.institution=20 AND mqas1.questionid=mq.id) * 100 ),2) AS percentage_facility
    
    from mdl_question mq 
    LEFT JOIN mdl_question_attempts mqa ON mq.id = mqa.questionid
    left join mdl_my_ques_type_data qtd on (qtd.question_id=mq.id) 
    left join mdl_my_ques_attributes qa on (qa.question=mq.id)
    left join specilities as s on(s.sp_id=qtd.spec) 
    where NOT mq.is_deleted
    and ((qa.status = 1) OR (qa.insti_id = 20 and qa.status = 0)) 
    GROUP BY mq.id 
    ORDER by mq.sort_order desc, mq.id DESC
    LIMIT 0,50;

Tags: mysql sql

Answers

Sorting (or ordering by) should not be the problem.

Can you take a look at this:

explain select 
    mq.id, mq.ques_type_id, mq.qtype, mq.name, mq.qimg_gallary_ids, 
    qtd.question_id, qtd.year, qtd.spec, qtd.subspec, qtd.subsubspec, 
    qa.status, qa.review_id, qa.diff_id, qa.land_id, qa.insti_id, qa.exclude_self_learning, 
    COUNT(mqa.questionid) AS total_students,
    SUM(CASE WHEN is_correct = 1 then 1 else 0 end) passed_students,
    round((SUM(CASE WHEN is_correct = 1 then 1 else 0 end) / COUNT(mqa.questionid) * 100 ),2) AS percentage,
    sq.total_students_facility,
    sq.passed_students_facility,
    ROUND((sq.passed_students_facility / sq.total_students_facility * 100 ),2) AS percentage_facility
    from mdl_question mq 
    LEFT JOIN mdl_question_attempts mqa ON mq.id = mqa.questionid
    left join mdl_my_ques_type_data qtd on (qtd.question_id=mq.id) 
    left join mdl_my_ques_attributes qa on (qa.question=mq.id)
    left join specilities as s on(s.sp_id=qtd.spec)
    left join (
        SELECT 
               COUNT(mqas1.questionid) total_students_facility,
               SUM(CASE WHEN mqas1.is_correct=1 THEN 1 END) passed_students_facility
        FROM mdl_question_attempts mqas1 
        LEFT JOIN mdl_quiz_attempts mquizas1 ON mqas1.questionusageid=mquizas1.uniqueid
        LEFT JOIN mdl_user us1 ON mquizas1.userid=us1.id
        WHERE us1.institution=20 
        GROUP BY mqas1.questionid
    ) as sq on questionid=mq.id
    where NOT mq.is_deleted
    and ((qa.status = 1) OR (qa.insti_id = 20 and qa.status = 0)) 
    GROUP BY mq.id 
    ORDER by mq.sort_order desc, mq.id DESC
    LIMIT 0,50;

The subqueries are moved to a LEFT JOIN, and the explain plan is looking quicker. (But I cannot test, because no data is available)

see: DBFIDDLE

- RickJames
- November 23, 2022 at 7:04 am
- 0 votes
0
Start by doing the percent in a single query.
```
( SELECT FORMAT(100 * SUM(mqas1.is_correct=1) / COUNT(*), 2)
    FROM ...
    LEFT JOIN ...
    LEFT JOIN ...
    WHERE  us1.institution=20
      AND  mqas1.questionid=mq.id
) AS percentage_facility
```
For clarity, use INNER JOIN unless you really need LEFT.

Can
```
    GROUP BY  mq.id
    ORDER by  mq.sort_order desc, mq.id DESC
```
be turned into
```
    GROUP by  mq.sort_order     , mq.id 
    ORDER by  mq.sort_order desc, mq.id desc
```
If it can, then it avoids a temp table and a sort.

Having columns from two different tables in a single WHERE makes optimization quite difficult.

Can qa.status be anything other than 0 or 1?

BIGINT takes 8 bytes each. There are much smaller sizes. For 9M rows, the savings adds up.

When you have a UNIQUE, consider using last the PRIMARY KEY and avoiding the id altogether.

You will probably never search on middlename, so why have an index? Or consider throwing several text columns into a single FULLTEXT-indexed column.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.