php - how to split a large query to a small queries and append them as a single query -

php - how to split a large query to a small queries and append them as a single query -

i have big query taking more time execute. want split query little queries , excute them , append them single result.

select mm.member_id, (select subcategory_name member_subcategory mm.admiral_type_id = subcategory_id) admiral_type_id, (select subcategory_name member_subcategory mm.royalty_type_id = subcategory_id) royalty_type_id, (select subcategory_name member_subcategory mm.parent_type_id = subcategory_id) parent_type_id, (select subcategory_name member_subcategory mm.guest_type_id = subcategory_id) guest_type_id, mha.first_name, mha.middle_name, mha.last_name, mha.address, mha.city, mha.state, mha.zip, mwa.first_name work_first_name, mwa.middle_name work_middle_name, mwa.last_name work_last_name, mwa.city work_city, mwa.state work_state, mwa.zip work_zip, mrm.year royalty_year, mrm.title royalty_title, mrm.first royalty_first, mrm.maiden royalty_maiden, (case when mha.preferred=0 'work address' when mha.preferred=1 'home address' end) preferred, (select msi.maidenname spouse_maidenname member_spouse_info msi msi.member_id = mm.member_id) spouse_maidenname, (select msi.mobile spouse_mobile member_spouse_info msi msi.member_id = mm.member_id) spouse_mobile, (select mai.fleet_year fleet_year member_admiral_info mai mai.member_id = mm.member_id) fleet_year, mmt.admiral_title, mmt.spouse_title, mmt.couple_title, (select mdi.year duchess_year member_duchess_info mdi mdi.member_id = mm.member_id) duchess_year, (select mdi.college duchess_college member_duchess_info mdi mdi.member_id = mm.member_id) duchess_college, (select mdi.major duchess_major member_duchess_info mdi mdi.member_id = mm.member_id) duchess_major, (select mdi.sorority duchess_sorority member_duchess_info mdi mdi.member_id = mm.member_id) duchess_sorority, (select mdi.parent_name duchess_parent_name member_duchess_info mdi mdi.member_id = mm.member_id) duchess_parent_name, (select mdi.escort_name duchess_escort_name member_duchess_info mdi mdi.member_id = mm.member_id) duchess_escort_name, (select dp.duchess_note duchess_note duchess_payment dp dp.duchess_id = (select mdi.duchess_id member_duchess_info mdi mdi.member_id = mm.member_id)) duchess_note, (select em.year escort_year escort_master em em.member_id = mm.member_id) escort_year, (select em.name escort_name escort_master em em.member_id = mm.member_id) escort_name, (select em.parent_name escort_parent_name escort_master em em.member_id = mm.member_id) escort_parent_name, (select em.notes escort_note escort_master em em.member_id = mm.member_id) escort_note, (select guest.guest_name invitee guest.member_id = mm.member_id) guest_name, (select (case when mpm.status=0 'current' when mpm.status=1 'past due' when mpm.status=2 'paid' end) payment_status member_payment_master mpm mpm.member_id = mm.member_id) payment_status, (select mpm.payment_date member_payment_master mpm mpm.member_id = mm.member_id) payment_date, (select mpm.payment_method member_payment_master mpm mpm.member_id = mm.member_id) payment_method, (select mpm.payment_amount member_payment_master mpm mpm.member_id = mm.member_id) payment_amount member_master mm, member_home_address mha, member_work_address mwa, member_mailing_title mmt, member_royalty_master mrm mha.member_id = mm.member_id , mwa.member_id = mm.member_id , mmt.member_id = mm.member_id , mrm.member_id = mm.member_id order mm.member_id;

breaking query , putting pieces, optimization purposes... not idea. dbms engine should able optimize things on own, if asking clear.

subqueries not evil, per se. rdbms (such firebird) can handle many subqueries on same tables , optimize plan access required tables once. unfortunately not rdbms this. so, removing subqueries may help out, in cases fetching several columns same table.

another issue old bring together syntax (using conditions). advised utilize explicit join... on syntax. may have impact on optimization.

here optimized version of query, should seek before more extreme measures:

select mm.member_id, sca.subcategory_name admiral_type_id, scr.subcategory_name royalty_type_id, scp.subcategory_name parent_type_id, scg.subcategory_name guest_type_id, mha.first_name, mha.middle_name, mha.last_name, mha.address, mha.city, mha.state, mha.zip, mwa.first_name work_first_name, mwa.middle_name work_middle_name, mwa.last_name work_last_name, mwa.city work_city, mwa.state work_state, mwa.zip work_zip, mrm.year royalty_year, mrm.title royalty_title, mrm.first royalty_first, mrm.maiden royalty_maiden, (case when mha.preferred=0 'work address' when mha.preferred=1 'home address' end) preferred, msi.maidenname spouse_maidenname, msi.mobile spouse_mobile, mai.fleet_year fleet_year, mmt.admiral_title, mmt.spouse_title, mmt.couple_title, mdi.year duchess_year, mdi.college duchess_college, mdi.major duchess_major, mdi.sorority duchess_sorority, mdi.parent_name duchess_parent_name, mdi.escort_name duchess_escort_name, dp.duchess_note duchess_note, em.year escort_year, em.name escort_name, em.parent_name escort_parent_name, em.notes escort_note, g.guest_name guest_name, (case when mpm.status=0 'current' when mpm.status=1 'past due' when mpm.status=2 'paid' end) payment_status, mpm.payment_date payment_date, mpm.payment_method payment_method, mpm.payment_amount payment_amount member_master mm bring together member_home_address mha on mha.member_id = mm.member_id bring together member_work_address mwa on mwa.member_id = mm.member_id bring together member_mailing_title mmt on mmt.member_id = mm.member_id bring together member_royalty_master mrm on mrm.member_id = mm.member_id left bring together member_spouse_info msi on msi.member_id = mm.member_id left bring together member_admiral_info mai on mai.member_id = mm.member_id left bring together member_duchess_info mdi on mdi.member_id = mm.member_id left bring together duchess_payment dp on dp.duchess_id = mdi.duchess_id left bring together escort_master em on em.member_id = mm.member_id left bring together invitee g on g.member_id = mm.member_id left bring together member_payment_master mpm on mpm.member_id = mm.member_id left bring together member_subcategory sca on sca.subcategory_id = mm.admiral_type_id left bring together member_subcategory scr on scr.subcategory_id = mm.royalty_type_id left bring together member_subcategory scp on scp.subcategory_id = mm.parent_type_id left bring together member_subcategory scg on scg.subcategory_id = mm.guest_type_id order mm.member_id;

notice have used many left joins. because have no thought if rows must exist or not in related tables. have not told that. query performance optimized farther using (inner) joins instead of left joins on tables must have matching row.

i hope have foreign key costraints on tables... or @ to the lowest degree indexes back upwards relations.

php mysql