sql - Best way to get results for mysql -
i use project i'm working on summer internship. need know if there better/easier way. i've got 9 tables information need consolidate 1 table. project meant limiting access based specific user based on cl_no , c_no. want distinct c_no cl_no based on table. started writing following script view see relevant records i'll working with.
select distinct `contacts_callmeet`.`c_no` `c_no`, `contacts_callmeet`.`ccm_cl_no` `ccm_cl_no`, 1 `ti_no`, 1 `ctc_status`, `contacts_callmeet`.`a_no` `a_no` `contacts_callmeet` ((`contacts_callmeet`.`ccm_status` = 1) , (`contacts_callmeet`.`ccm_cl_no` not null) , (`contacts_callmeet`.`ccm_cl_no` <> 0)) union select distinct `contacts_notes`.`c_no` `c_no`, `contacts_notes`.`cl_no` `cl_no`, 2 `ti_no`, 1 `ctc_status`, `contacts_notes`.`a_no` `a_no` `contacts_notes` ((`contacts_notes`.`cn_status` = 1) , (`contacts_notes`.`cl_no` not null) , (`contacts_notes`.`cl_no` <> 0) , (`contacts_notes`.`c_no` not null)) union select distinct `d`.`c_no` `c_no`, `d`.`cl_no` `cl_no`, 3 `ti_no`, 1 `ctc_status`, `d`.`a_no` `a_no` `disbursements` `d` ((`d`.`disb_status` = 1) , (`d`.`cl_no` not null) , (`d`.`cl_no` <> 0) , (`d`.`c_no` not null)) union select distinct `ea`.`c_no` `c_no`, `e`.`cl_no` `cl_no`, 4 `ti_no`, 1 `ctc_status`, `e`.`a_no` `a_no` (`events_attendees` `ea` join `events` `e` on ((`e`.`e_no` = `ea`.`e_no`))) ((`ea`.`ea_status` = 1) , (`e`.`e_status` = 1) , (`e`.`cl_no` not null) , (`e`.`cl_no` <> 0) , (`ea`.`c_no` not null)) union select distinct `gj`.`c_no` `c_no`, `g`.`cl_no` `cl_no`, 5 `ti_no`, 1 `ctc_status`, `g`.`a_no` `a_no` (`groups` `g` join `groups_join` `gj` on ((`g`.`g_no` = `gj`.`g_no`))) ((`g`.`g_status` = 1) , (`g`.`cl_no` not null) , (`g`.`cl_no` <> 0) , (`gj`.`c_no` not null)) union select distinct `pledges`.`c_no` `c_no`, `pledges`.`cl_no` `cl_no`, 6 `ti_no`, 1 `ctc_status`, `pledges`.`a_no` `a_no` `pledges` ((`pledges`.`pl_status` = 1) , (`pledges`.`cl_no` not null) , (`pledges`.`cl_no` <> 0) , (`pledges`.`c_no` not null)) union select distinct `ec`.`c_no` `c_no`, `e`.`cl_no` `cl_no`, 7 `ti_no`, 1 `ctc_status`, `e`.`a_no` `a_no` (`events_cohosts` `ec` join `events` `e` on ((`e`.`e_no` = `ec`.`e_no`))) ((`ec`.`ec_status` = 1) , (`e`.`e_status` = 1) , (`e`.`cl_no` not null) , (`e`.`cl_no` <> 0) , (`ec`.`c_no` not null)) union select distinct `cl`.`c_no` `c_no`, `clp`.`cl_no` `cl_no`, 8 `ti_no`, 1 `ctc_status`, `cl`.`a_no` `a_no` (`comm_list` `cl` join `comm_list_parent` `clp` on ((`clp`.`clp_no` = `cl`.`clp_no`))) ((`clp`.`clp_status` = 1) , (isnull(`cl`.`coml_status`) or (`cl`.`coml_status` = 1)) , (`clp`.`cl_no` not null) , (`clp`.`cl_no` <> 0) , (`cl`.`c_no` not null)) union select distinct `commitments`.`c_no` `c_no`, `commitments`.`cl_no` `cl_no`, 9 `ti_no`, 1 `ctc_status`, `commitments`.`a_no` `a_no` `commitments` ((`commitments`.`commit_status` = 1) , (`commitments`.`cl_no` not null) , (`commitments`.`cl_no` <> 0) , (`commitments`.`c_no` not null)) then realized runtime far long (50+ secs) started writing triggers each table populate table called contacts_table_clients(seen below). triggers 1 table can seen below.
ctc_no, c_no, cl_no, ti_no, ctc_status, a_no
88, 6972179, 2000, 2, 1, 151
create definer=`idpadmin`@`%` trigger `idp1`.`contacts_notes_after_insert` after insert on `contacts_notes` each row begin declare records int(10); if new.cl_no not null select count(c_no) records contacts_notes cn_status = 1 , c_no = new.c_no , cl_no not null , cl_no not in (select cl_no contacts_table_clients ctc_status = 1 , c_no = new.c_no , ti_no = 2); end if; if records > 0 insert `idp1`.`contacts_table_clients` (`c_no`, `cl_no`, `ti_no`, `a_no`) values (new.c_no, new.cl_no, 2, new.a_no); end if; create definer=`idpadmin`@`%` trigger `idp1`.`contacts_notes_after_update` after update on `contacts_notes` each row begin declare old_cl_no int(10); declare new_cl_no int(10); declare delete_c_no int(10); declare delete_cl_no int(10); declare records int(10); declare records_2 int(10); declare old_cl_no_select int(10); declare new_cl_no_select int(10); if new.cl_no > 0 , old.cl_no null drop temporary table if exists tmp_contacts_notes_2; create temporary table tmp_contacts_notes_2 select c_no, cl_no,a_no contacts_notes cn_status = 1 , c_no = new.c_no , cl_no not null , cl_no not in (select cl_no contacts_table_clients ctc_status = 1 , ti_no = 2 , c_no = new.c_no); select count(*) records_2 tmp_contacts_notes_2; end if; if records_2 > 0 insert `idp1`.`contacts_table_clients` (`c_no`, `cl_no`, `ti_no`, `a_no`) values (new.c_no, new.cl_no, 2, new.a_no); end if; if new.cn_status = 0 or new.cl_no null select distinct c_no delete_c_no contacts_table_clients ctc_status = 1 , ti_no = 2 , c_no = new.c_no , cl_no not in (select cl_no contacts_notes c_no = new.c_no , cn_status = 1 , cl_no not null); select distinct cl_no delete_cl_no contacts_table_clients ctc_status = 1 , ti_no = 2 , c_no = new.c_no , cl_no not in (select cl_no contacts_notes c_no = new.c_no , cn_status = 1 , cl_no not null); update contacts_table_clients set ctc_status = 0 ti_no = 2 , c_no = delete_c_no , cl_no = delete_cl_no; end if; if new.cl_no <> old.cl_no , new.cl_no not null , new.cl_no <> 0 select cl_no old_cl_no contacts_table_clients ti_no = 2 , ctc_status = 1 , c_no = new.c_no , cl_no not in (select cl_no contacts_notes c_no = new.c_no , cn_status = 1 , cl_no not null); select cl_no new_cl_no contacts_notes c_no = new.c_no , cn_status = 1 , cl_no not in (select cl_no contacts_table_clients ti_no = 2 , c_no = new.c_no , ctc_status = 1 , cl_no not null); end if; if new_cl_no > 0 , old_cl_no > 0 update contacts_table_clients set cl_no = new_cl_no ctc_status = 1 , ti_no = 1 , c_no = new.c_no , cl_no = old_cl_no; end if; if new.cl_no <> old.cl_no , new.cl_no not null , new.cl_no <> 0 select distinct cl_no delete_cl_no contacts_table_clients ctc_status = 1 , ti_no = 2 , c_no = new.c_no , cl_no not in (select cl_no contacts_notes c_no = new.c_no , cn_status = 1 , cl_no not null); update contacts_table_clients set ctc_status = 0 ti_no = 2 , c_no = new.c_no , cl_no = delete_cl_no; end if; if delete_cl_no < 0 , (old_cl_no null or new_cl_no null or new_cl_no < 1 or old_cl_no < 1) insert `idp1`.`contacts_table_clients` (`c_no`, `cl_no`, `ti_no`, `a_no`) values (new.c_no, new.cl_no, 2, new.a_no); end if; if new.cl_no <> old.cl_no , old_cl_no > 0 , new.cl_no not null drop temporary table if exists tmp_contacts_notes; create temporary table tmp_contacts_notes select c_no, cl_no,a_no contacts_notes cn_status = 1 , c_no = new.c_no , cl_no not null , cl_no not in (select cl_no contacts_table_clients ctc_status = 1 , ti_no = 2 , c_no = new.c_no); select count(*) records tmp_contacts_notes; end if; if records > 0 insert `idp1`.`contacts_table_clients` (`c_no`, `cl_no`, `ti_no`, `a_no` ) values (new.c_no, new.cl_no, 2, new.a_no); end if; if new.cl_no <> old.cl_no , new.cl_no not null , new.cl_no <> 0 select count(cl_no) old_cl_no_select contacts_notes c_no = new.c_no , cn_status = 1 , cl_no not null , cl_no not in (select cl_no contacts_table_clients ti_no = 2 , ctc_status = 1 , c_no = new.c_no); end if; if old_cl_no_select > 0 insert `idp1`.`contacts_table_clients` (`c_no`, `cl_no`, `ti_no`, `a_no`) values (new.c_no, new.cl_no, 2, new.a_no); end if; end thank help!
the union ok.
start separating sql statements , observe how long each 1 takes.
i guess 1 or 2 of them queries pretty large table. explain find out takes long. there indexes missing.
add them this:
alter table `contacts_callmeet` add index `ccm_status` (`ccm_status`) i had issue lately , solved in similar way. seems mysql handles separate table indexes better using joins and/or unions.
the approach (i had performance boost of 1500x on first query), thorsten said: think whether want distinct before union. maybe want that, guess results faster when distinct in end.
Comments
Post a Comment