To optimize the given query, you can make the following changes:

  1. Add appropriate indexes: Make sure that all the columns used in join conditions and where clauses have proper indexes. This will help improve the query performance.

  2. Use INNER JOIN instead of LEFT JOIN: If you can guarantee that there will always be matching records in the joined tables, you can use INNER JOIN instead of LEFT JOIN. INNER JOIN generally performs better than LEFT JOIN.

  3. Avoid using subqueries in JOIN clauses: Instead of using subqueries in the JOIN clauses, you can move the subqueries to the FROM clause and join them directly with the other tables. This can improve the query performance.

  4. Limit the number of columns in the SELECT clause: Select only the necessary columns instead of selecting all columns from the tables. This can reduce the amount of data transferred and improve performance.

  5. Use EXISTS instead of IN subquery: Instead of using the IN operator with a subquery, you can use the EXISTS operator. EXISTS generally performs better for large datasets.

Here's an optimized version of the query:

SELECT DISTINCT CONCAT(sub.question_id,'-',sub.sub_question_id), sub.question_id, sub.sub_question_id, sub.exam_id, sub.exam_pager_id, sub.course_id, sub.sub_type, sub.sub_type_name, sub.sub_title, sub.index_num, sub.structure_number, sub.parse, sub.answer1, sub.option_a, sub.option_b, sub.option_c, sub.option_d, sub.option_e, sub.option_f, sub.option_g, sub.option_h, sub.option_i, sub.option_j, sub.knowledges, sub.knowledge_ids, score.finally_score, IFNULL(print.print_count,0) AS print_count, cinfo.score_rate, sub.image, sub.image_height, sub.answer, sub.answer_height, sub.knowledge, sub.knowledge_height FROM mis_exam_sub_question AS sub INNER JOIN mis_school_exam_student AS student ON CONCAT(sub.school_id,'-',sub.exam_id) = CONCAT(student.school_id,'-',student.exam_id) INNER JOIN mis_school_exam_student_score AS score ON CONCAT(sub.school_id,'-',sub.exam_id,'-',sub.exam_pager_id,'-',sub.index_num) = CONCAT(score.school_id,'-',score.exam_id,'-',score.exam_paper_id,'-', score.index_num) AND CONCAT(student.school_class_id,'-',student.school_student_id) = CONCAT(score.school_class_id,'-',score.school_student_id) INNER JOIN (SELECT sub_question_id,knowledge_id, knowledge_name FROM mis_exam_sub_question_knowledge_info) AS kinfo ON sub.sub_question_id = kinfo.sub_question_id INNER JOIN (SELECT school_id, sub_question_id, SUM(IFNULL(print_count,0)) AS print_count FROM mis_exam_question_print_count GROUP BY sub_question_id) AS print ON print.sub_question_id = sub.sub_question_id INNER JOIN mis_exam_sub_question_class_score_info AS cinfo ON CONCAT(sub.sub_question_id,'-',student.school_class_id) = CONCAT(cinfo.sub_question_id,'-',cinfo.school_class_id) LEFT JOIN mis_school_exam AS exam ON exam.exam_id = sub.exam_id AND exam.school_id = sub.school_id WHERE sub.school_id = 1441 AND sub.exam_id = 26944 AND sub.exam_pager_id = 152871 AND sub.course_id = 1 AND student.school_student_id = 1923955 AND sub.question_id = 2163587 AND EXISTS ( SELECT 1 FROM mis_exam_sub_question_knowledge_info WHERE sub.sub_question_id = mis_exam_sub_question_knowledge_info.sub_question_id AND knowledge_id IN ("43199","46046","46047","46051","46133","46140","46153","46169","46170","46178","46227","46230","47315","47652","47653","49058","51507","51532","51548","51549","51605","52064","52365","52390","52408","52412","52416","52419","53752","53851","53853","53890","65446","65540","65771","65857","69038","70048","70049","70051","70426","70930","71821","71822","71836","71844","71845","71855","71903","71916","71917","71918","71930","71936","71941","71949","71957","71961","74564","79834","79836","79850","79851","79861","79865","80027","80743","80781","80783","80796","80820","80824","80826","80883","80893","81059","81154","81265","81811","82173","82174","82190","82204","82225","82226","82243") ) AND score.score > score.finally_score;

Make sure to create appropriate indexes on the join columns and columns used in the WHERE clause for better performance

EXPLAIN SELECT distinct CONCATsubquestion_id-subsub_question_id subquestion_id subsub_question_id subexam_id subexam_pager_id subcourse_id subsub_type subsub_type_name subsub_title subindex_num substr

原文地址: https://www.cveoy.top/t/topic/iQB0 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录