mysql - 优化mysql联合查询

Question

我编写了以下查询来从两组不同的表中检索一组数据。

`SELECT `chapter_id`, `chapter_title`, `actual_content_type`, `area_name`, `status`
FROM ((
      SELECT `css`.`chapter_id`, `css`.`chapter_title`, `a`.`name` AS 
      `area_name`, `css`.`content_status` AS `status`, `css`.`actual_content_type`
      FROM `dm_content_search_summary` `css`
      INNER JOIN `curriculum` `cu` ON css.curriculum_id=cu.id
      INNER JOIN `year` `y` ON css.year_id=y.id
      INNER JOIN `area` `a` ON y.area_id=a.id
      INNER JOIN `country` `cou` ON a.country_id=cou.id
    ) 
      UNION ALL (
       SELECT `cpx`.`category_id` AS `chapter_id`, `cat`.`name` AS 
            `chapter_title`, `a`.`name` AS `area_name`, `pa`.`status` AS 
            `status`, 'PROVE_IT' AS actual_content_type
       FROM `qbnk_published_assessment` `pa`
       INNER JOIN `qbnk_assessment` `ass` ON pa.assessment_id=ass.id
       INNER JOIN `qbnk_assessment_question_xref` `aq` ON 
                ass.id=aq.assessment_id
       INNER JOIN `qbnk_question` `q` ON aq.question_id=q.id
       INNER JOIN `qbnk_answer` `an` ON q.id=an.question_id
       INNER JOIN `qbnk_category_published_assessment_xref` `cpx` ON 
            pa.id=cpx.published_assessment_id
       INNER JOIN `category` `cat` ON cpx.category_id=cat.id
       INNER JOIN `course` `co` ON cat.course_id=co.id
       INNER JOIN `curriculum` `cu` ON co.curriculum_id=cu.id
       INNER JOIN `year` `y` ON cu.year_id = y.id
       INNER JOIN `area` `a` ON y.area_id=a.id
       INNER JOIN `country` `cou` ON a.country_id=cou.id
         GROUP BY `pa`.`assessment_id`
  )
) `0` ORDER BY `chapter_title``

表之间的关系 Country-areas 为 1:many, area-year 为 1:many, area-curriculum 为 1:many, course-curriculum 为 1:many, course:category 为 1:many, dm_content_search_summary:curriculum 为 many:1

qbnk_published_assessment:qbnk_assessment 是 1:1，qbnk_assessment:qbnk_question 是 many:many，qbnk_question:qbnk_answer 是 1:many，qbnk_category_published_assessment_xref:qbnk_published_assessment 是 many:many

目前检索数据需要超过 17 秒。有什么办法可以改进这个查询吗？解释命令的结果如下。

=====================================更新============ =====================

有一些过滤器需要应用于此查询。这就是我加入一组表的原因。过滤字段为 cu.subject_id、cu.year_id、a.id、a.country_id 和 css.actual_content_type。我必须在 ass.name、q.description、an.label、qbnk_category_published_assessment_xref、css.chapter_content 和 css.content_title 字段中应用条件搜索词。本节的用户界面如下。

应用搜索词和过滤器进行查询后，如下所示。

SELECT `chapter_id`, `chapter_title`, `actual_content_type`, `area_name`, 
`status`
 FROM ((
   SELECT `css`.`chapter_id`, `css`.`chapter_title`, `a`.`name` AS 
   `area_name`, `css`.`content_status` AS `status`, 
   `css`.`actual_content_type`
    FROM `country` `cou`
    INNER JOIN `area` `a` ON cou.id=a.country_id AND a.country_id=4
    INNER JOIN `year` `y` ON a.id=y.area_id AND a.id=19
    INNER JOIN `curriculum` `cu` ON y.id=cu.year_id
    INNER JOIN `dm_content_search_summary` `css` ON cu.id=css.curriculum_id 
    AND cu.year_id=41 AND cu.subject_id=2
    WHERE (((((`css`.`content_title` LIKE '%Summary%') OR 
    (`css`.`chapter_content` LIKE '%Summary%')) AND ((`css`.`content_title` 
    LIKE '%and text%') OR (`css`.`chapter_content` LIKE '%and text%'))) OR 
    (`css`.`content_title` LIKE '%or text%')) OR (`css`.`chapter_content` 
    LIKE '%or text%')) AND ((css.actual_content_type='READ_IT' AND 
    css.chapter_content NOT LIKE '%not text%' AND css.content_title NOT LIKE 
   '%not text%') OR (css.actual_content_type<>'READ_IT' AND 
   css.content_title NOT LIKE '%not text%'))) UNION (
   SELECT `cpx`.`category_id` AS `chapter_id`, `cat`.`name` AS 
   `chapter_title`, `a`.`name` AS `area_name`, `pa`.`status` AS `status`, 
   'PROVE_IT' AS actual_content_type
   FROM `qbnk_published_assessment` `pa`
   INNER JOIN `qbnk_assessment` `ass` ON pa.assessment_id=ass.id
   INNER JOIN `qbnk_assessment_question_xref` `aq` ON 
     ass.id=aq.assessment_id
   INNER JOIN `qbnk_question` `q` ON aq.question_id=q.id
   INNER JOIN `qbnk_answer` `an` ON q.id=an.question_id
   INNER JOIN `qbnk_category_published_assessment_xref` `cpx` ON 
    pa.id=cpx.published_assessment_id
   INNER JOIN `category` `cat` ON cpx.category_id=cat.id
   INNER JOIN `course` `co` ON cat.course_id=co.id
   INNER JOIN `curriculum` `cu` ON co.curriculum_id=cu.id AND cu.year_id=41 
    AND cu.subject_id=2
   INNER JOIN `year` `y` ON cu.year_id = y.id
   INNER JOIN `area` `a` ON y.area_id=a.id AND a.id=19
   INNER JOIN `country` `cou` ON a.country_id=cou.id AND a.country_id=4
   WHERE ((((((((
     (`ass`.`name` LIKE '%Summary%') 
     OR (`q`.`description` LIKE '%Summary%')) 
     OR (`an`.`label` LIKE '%Summary%')) 
     AND (`ass`.`name` LIKE '%and text%') 
     AND (`q`.`description` LIKE '%and text%') 
     AND (`an`.`label` LIKE '%and text%')) 
     OR (`ass`.`name` LIKE '%or text%')) 
     OR (`q`.`description` LIKE '%or text%'))
     OR (`an`.`label` LIKE '%or text%')) 
     OR (`ass`.`name` NOT LIKE '%not text%')) 
     OR (`q`.`description` NOT LIKE '%not text%')) 
    OR (`an`.`label` NOT LIKE '%not text%')
 GROUP BY `pa`.`assessment_id`)) `0`
 ORDER BY `chapter_title`

谢谢

标签： mysqlquery-optimization