sql - MySQL Query too large, is there a way to opimize? -

is there way optimize large mysql query? have tried many things, give me results not correct, such duplicates , empty columns.

select quotations.*,   group_concat(distinct concat(groups.group_id, " | ", groups.group_name) separator " , ") `quotation_groups`, group_concat(distinct concat(departments.department_id, " | ", departments.department_name, " | ", departments.department_code) separator " , ") `quotation_departments`, group_concat(distinct concat(projects.project_id) separator " , ") `quotation_projects`, group_concat(distinct concat(machines.machine_id, " | ", machines.machine_name) separator " , ") `quotation_machines`, group_concat(distinct concat(parts.part_id, " | ", parts.part_name) separator " , ") `quotation_parts`, group_concat(distinct concat(options.option_id, " | ", options.option_name) separator " , ") `quotation_options`, group_concat(distinct concat(applications.application_id, " | ", applications.application_nl_title) separator " , ") `quotation_applications`, group_concat(distinct concat(actions.action_id, " | ", actions.action_name) separator " , ") `quotation_actions`, group_concat(distinct concat(tests.test_id, " | ", tests.test_name) separator " , ") `quotation_tests`, group_concat(distinct concat(notes.note_id, " | ", notes.note_name) separator " , ") `quotation_notes`, group_concat(distinct concat(companies.company_id, " | ", companies.company_name) separator " , ") `quotation_companies`, group_concat(distinct concat(persons.person_id, " | ", persons.person_gender, " | ", persons.person_firstname, " | ", persons.person_middlename, " | ", persons.person_lastname, " | ", persons.person_function) separator " , ") `quotation_persons`, group_concat(distinct concat(addresses.address_id, " | ", addresses.address_types, " | ", addresses.address_name, " | ", addresses.address_streetname, " | ", addresses.address_housenumber, " | ", addresses.address_zipcode, " | ", addresses.address_city) separator " , ") `quotation_addresses`, group_concat(distinct concat(attachments.attachment_id, " | ", attachments.attachment_name) separator " , ") `quotation_attachments`   quotations quotations   left join quotations_relations quotation_departments on (quotation_departments.quotations_relations_child_name="departments" , quotation_departments.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_groups on (quotation_groups.quotations_relations_child_name="groups" , quotation_groups.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_projects on (quotation_projects.quotations_relations_child_name="projects" , quotation_projects.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_machines on (quotation_machines.quotations_relations_child_name="machines" , quotation_machines.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_parts on (quotation_parts.quotations_relations_child_name="parts" , quotation_parts.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_options on (quotation_options.quotations_relations_child_name="options" , quotation_options.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_applications on (quotation_applications.quotations_relations_child_name="applications" , quotation_applications.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_actions on (quotation_actions.quotations_relations_child_name="actions" , quotation_actions.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_tests on (quotation_tests.quotations_relations_child_name="tests" , quotation_tests.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_notes on (quotation_notes.quotations_relations_child_name="notes" , quotation_notes.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_companies on (quotation_companies.quotations_relations_child_name="companies" , quotation_companies.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_persons on (quotation_persons.quotations_relations_child_name="persons" , quotation_persons.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_addresses on (quotation_addresses.quotations_relations_child_name="addresses" , quotation_addresses.quotations_relations_parent=quotations.quotation_id) left join quotations_relations quotation_attachments on (quotation_attachments.quotations_relations_child_name="attachments" , quotation_attachments.quotations_relations_parent=quotations.quotation_id)   left join groups_relations groups_relations on (groups_relations.groups_relations_child_name="quotations" , groups_relations.groups_relations_child_id=quotations.quotation_id)  left join groups groups on (groups.group_id=groups_relations.groups_relations_parent or groups.group_id=quotation_groups.quotations_relations_child_id) left join departments_relations departments_relations on (departments_relations.departments_relations_child_name="quotations" , departments_relations.departments_relations_child_id=quotations.quotation_id)  left join departments departments on (departments.department_id=departments_relations.departments_relations_parent or departments.department_id=quotation_departments.quotations_relations_child_id) left join projects_relations projects_relations on (projects_relations.projects_relations_child_name="quotations" , projects_relations.projects_relations_child_id=quotations.quotation_id)  left join projects projects on (projects.project_id=projects_relations.projects_relations_parent or projects.project_id=quotation_projects.quotations_relations_child_id) left join machines_relations machines_relations on (machines_relations.machines_relations_child_name="quotations" , machines_relations.machines_relations_child_id=quotations.quotation_id)  left join machines machines on (machines.machine_id=machines_relations.machines_relations_parent or machines.machine_id=quotation_machines.quotations_relations_child_id) left join parts_relations parts_relations on (parts_relations.parts_relations_child_name="quotations" , parts_relations.parts_relations_child_id=quotations.quotation_id)  left join parts parts on (parts.part_id=parts_relations.parts_relations_parent or parts.part_id=quotation_parts.quotations_relations_child_id) left join options_relations options_relations on (options_relations.options_relations_child_name="quotations" , options_relations.options_relations_child_id=quotations.quotation_id)  left join options options on (options.option_id=options_relations.options_relations_parent or options.option_id=quotation_options.quotations_relations_child_id) left join applications_relations applications_relations on (applications_relations.applications_relations_child_name="quotations" , applications_relations.applications_relations_child_id=quotations.quotation_id)  left join applications applications on (applications.application_id=applications_relations.applications_relations_parent or applications.application_id=quotation_applications.quotations_relations_child_id) left join actions_relations actions_relations on (actions_relations.actions_relations_child_name="quotations" , actions_relations.actions_relations_child_id=quotations.quotation_id)  left join actions actions on (actions.action_id=actions_relations.actions_relations_parent or actions.action_id=quotation_actions.quotations_relations_child_id) left join tests_relations tests_relations on (tests_relations.tests_relations_child_name="quotations" , tests_relations.tests_relations_child_id=quotations.quotation_id)  left join tests tests on (tests.test_id=tests_relations.tests_relations_parent or tests.test_id=quotation_tests.quotations_relations_child_id) left join notes_relations notes_relations on (notes_relations.notes_relations_child_name="quotations" , notes_relations.notes_relations_child_id=quotations.quotation_id)  left join notes notes on (notes.note_id=notes_relations.notes_relations_parent or notes.note_id=quotation_notes.quotations_relations_child_id) left join companies_relations companies_relations on (companies_relations.companies_relations_child_name="quotations" , companies_relations.companies_relations_child_id=quotations.quotation_id)  left join companies companies on (companies.company_id=companies_relations.companies_relations_parent or companies.company_id=quotation_companies.quotations_relations_child_id) left join persons_relations persons_relations on (persons_relations.persons_relations_child_name="quotations" , persons_relations.persons_relations_child_id=quotations.quotation_id)  left join persons persons on (persons.person_id=persons_relations.persons_relations_parent or persons.person_id=quotation_persons.quotations_relations_child_id) left join addresses_relations addresses_relations on (addresses_relations.addresses_relations_child_name="quotations" , addresses_relations.addresses_relations_child_id=quotations.quotation_id)  left join addresses addresses on (addresses.address_id=addresses_relations.addresses_relations_parent or addresses.address_id=quotation_addresses.quotations_relations_child_id) left join attachments_relations attachments_relations on (attachments_relations.attachments_relations_child_name="quotations" , attachments_relations.attachments_relations_child_id=quotations.quotation_id)  left join attachments attachments on (attachments.attachment_id=attachments_relations.attachments_relations_parent or attachments.attachment_id=quotation_attachments.quotations_relations_child_id)   group quotations.quotation_id order quotations.quotation_updated desc 

as can see, need details every table have relation eachother.

i noticed, that, after while, more relations there are, slower query is, example, have quotation 103 relations, takes 34seconds gather data.

my first suggestion single join conditional aggregation. not need distinct in group_concat() more. here example departments:

select q.*,        group_concat(case when qr.quotations_relations_child_name = 'departments' qr.department_id, ' | ', qr.department_name end) quotations q left join      quotations_relations qr      on qr.quotations_relations_parent = q.quotation_id group q.quotation_id order q.quotation_updated desc ;