0

Just wondering if this query could be optimized or if sub-queries would be better?

select t.entry_id, 
t.title, 
d.field_id_249 as unique_id, 
d.field_id_577 as keywords,
d.field_id_681 AS video,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_name ELSE NULL END SEPARATOR ",") as subject_name, 
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_url_title ELSE NULL END SEPARATOR ",") as subject_url, 
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_order ELSE NULL END SEPARATOR ",") as subject_order,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_name ELSE NULL END SEPARATOR ",") as topic_name, 
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_url_title ELSE NULL END SEPARATOR ",") as topic_url, 
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_order ELSE NULL END SEPARATOR ",") as topic_order,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw2.title ELSE NULL END SEPARATOR ",") as software_name,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw2.url_title ELSE NULL END SEPARATOR ",") as software_url,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw3.field_id_166 ELSE NULL END SEPARATOR ",") as software_icon,
GROUP_CONCAT(DISTINCT grade.cat_description SEPARATOR ",") as grades, 
GROUP_CONCAT(DISTINCT CASE WHEN ab.grade_code != "" THEN ab.grade_code ELSE NULL END SEPARATOR ",") as ab_grade, 
GROUP_CONCAT(DISTINCT ab.state SEPARATOR ",") as ab_state, 
GROUP_CONCAT(DISTINCT ab.state_id SEPARATOR ",") as ab_standards
from exp_channel_titles AS t
inner join exp_playa_relationships as p on (t.entry_id = p.child_entry_id)
left join exp_channel_data as d on (t.entry_id = d.entry_id)
left join api_ab AS ab on (ab.unique_id = d.field_id_249)
left join exp_category_posts AS cp on (cp.entry_id = p.parent_entry_id)
left join exp_categories AS sub on (sub.cat_id = cp.cat_id)
left join exp_playa_relationships AS sw1 on (sw1.parent_entry_id = t.entry_id)
left join exp_channel_titles AS sw2 on (sw2.entry_id = sw1.child_entry_id)
left join exp_channel_data AS sw3 on (sw2.entry_id = sw3.entry_id)
left join exp_category_posts AS gr on (t.entry_id = gr.entry_id)
left join exp_categories AS grade on (grade.cat_id = gr.cat_id)
where `p`.`parent_field_id` = 678 AND `t`.`status` = 'open' AND FIND_IN_SET("animal", d.field_id_577) OR `t`.`title` LIKE '%animal%'
group by p.child_entry_id
1

You are generating logically unreasonable cartesian product and then technically "fixing" it with DISTINCT (If you survive the generated set volume).
Yes, sub-queries is definitly the way to go here.

  • Got it! Makes sense - but could you give an example of a sub-query that still returns values as group_concat distinct? – webmaster_sean Feb 6 '17 at 18:36
  • No problem, but tomorrow – David דודו Markovitz Feb 6 '17 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.