mysql - Query with union repeating same subquery into each row -

in db have 5 tables details and, extract details each table, need join witn 3 more tables.
need (into result query table) data (always same data each idlite) , add them each row.
wrote query work very slow can't use it.
used concat 2 times:
first time details each detail table;
second time data others tables (these data same). data before concat same (for each idlite).
how can improve query? thinking using variables wasn't able it.

( select     t.office,     li.year,     li.rifnum,     1 idtable,     li.idlite,     idliteuser,     d1.idevent,      concat_ws('_',         ifnull(d1.typet,''),          ifnull(d1.in_date,''),          ifnull(d1.out_date,'')         ) dett,     concat_ws('_',          ifnull(li.section,''),          ifnull(li.indate,''),          ifnull(li.type,''),          ifnull(li.subject,''),          ifnull(li.dcu_instance,''),          ifnull(li.dc_instance,'')         ) essential  trdetails1 d1  inner join truserlite lu using (idlite)  inner join trlite li using (idlite)  inner join tr t using(idoffice)  lu.iduser= '@myvarinsertedeachtime'  , lu.status = 0  , d1.idevent not in (     select idevent      trinfo      iduser= '@myvarinsertedeachtime'      , idtable = 1      , idevent = d1.idevent      , date > d1.changed) ) union  (  select      t.office,      li.year,      li.rifnum,      2 idtable,      li.idlite,      idliteuser,      d2.idevent,      concat_ws('_',          ifnull(doctype,''),          ifnull(d2.number,''),          ifnull(d2.side,''),          ifnull(d2.indate,'')         ) dett,      concat_ws('_',          ifnull(li.section,''),          ifnull(li.indate,''),          ifnull(li.type,''),          ifnull(li.subject,''),          ifnull(li.dcu_instance,''),          ifnull(li.dc_instance,'')         ) essential  trdetails2 d2  inner join truserlite lu using (idlite)  inner join trlite li using (idlite)  inner join tr t using(idoffice)  lu.iduser= '@myvarinsertedeachtime'  , lu.status = 0  , d2.idevent not in (     select idevent      trinfo      iduser= lu.iduser     , idtable= 2)  ) union  (  select      t.office,      li.year,      li.rifnum,      3 idtable,      li.idlite,      idliteuser,      d3.idevent,      concat_ws('_',          ifnull(d3.udidate,''),          ifnull(d3.uditype,''),          ifnull(d3.udiresult,''),          ifnull(d3.supervisor,''),          ifnull(d3.groupin,''),          ifnull(d3.rorder,'')         ) dett,      concat_ws('_',          ifnull(li.section,''),          ifnull(li.indate,''),          ifnull(li.type,''),          ifnull(li.subject,''),          ifnull(li.dcu_instance,''),          ifnull(li.dc_instance,'')         ) essential  trdetails3 d3  inner join truserlite lu using (idlite)  inner join trlite li using (idlite)  inner join tr t using(idoffice)  lu.iduser= '@myvarinsertedeachtime'  , lu.status = 0  , d3.idevent not in (     select idevent     trinfo     iduser= lu.iduser     , idtable= 3)  ) union  (  select      t.office,      li.year,      li.rifnum,      4 idtable,      li.idlite,      idliteuser,      d4.idevent,      concat_ws('_',          ifnull(d4.type,''),          ifnull(d4.number,''),          ifnull(d4.supervisor,''),          ifnull(d4.pubdate,''),          ifnull(d4.udidate,''),          ifnull(d4.uditype,''),          ifnull(d4.result,'')         ) dett,      concat_ws('_',          ifnull(li.section,''),          ifnull(li.indate,''),          ifnull(li.type,''),          ifnull(li.subject,''),          ifnull(li.dcu_instance,''),          ifnull(li.dc_instance,'')         ) essential  trdetails4 d4  inner join truserlite lu using (idlite)  inner join trlite li using (idlite)  inner join tr t using(idoffice)  lu.iduser= '@myvarinsertedeachtime'  , lu.status = 0  , d4.idevent not in (     select idevent     trinfo     iduser= lu.iduser     , idtable= 4)  ) union  (  select      t.office,      li.year,      li.rifnum,      5 idtable,      li.idlite,      idliteuser,      d5.idevent,      concat_ws('_',          ifnull(d5.type,''),          ifnull(d5.number,''),          ifnull(d5.supervisor,''),          ifnull(d5.pubdate,''),          ifnull(d5.result,'')         ) dett,      concat_ws('_',          ifnull(li.section,''),          ifnull(li.indate,''),          ifnull(li.type,''),          ifnull(li.subject,''),          ifnull(li.dcu_instance,''),          ifnull(li.dc_instance,'')         ) essential  trdetails5 d5  inner join truserlite lu using (idlite)  inner join trlite li using (idlite)  inner join tr t using(idoffice)  lu.idutente = '@myvarinsertedeachtime'  , lu.status = 0  , d5.idevent not in (     select idevent      trinfo     iduser= lu.idutente      , idtable= 5) ) order office, year, rifnum, idtable  

edit:
structure of db is:
idlite id of basic data.
each idlite has 1 office, 1 year, 1 rifnum, 1 tr.
each idlite has many records on each of 5 tables of details (trdetails1, trdetails2, trdetails3, trdetails4, trdetails5).

each user has many idlite (stored ref in truserlite) , need know each event (stored each of 5 tables) of each idlite.

when user knows detail (rif) stored trinfo

i need retrieve events (from 5 tables) interesting each idlite (linked specific user) not known him.

edit2:
these tables structures:

create table if not exists `trdetails1` (   `idevent` bigint(20) not null auto_increment primary key ,   `idlite` bigint(20) not null,   `typet` varchar(50) default null,   `in_date` date default null,   `out_date` date default null,   `changed` timestamp not null default current_timestamp on update current_timestamp ) engine=innodb;  create table if not exists `trdetails2` (   `idevent` bigint(20) not null auto_increment primary key,   `idlite` bigint(20) not null,   `doctype` varchar(200) default null,   `number` int(10) default null,   `side` varchar(50) default null,   `indate` date default null,   `updated` timestamp not null default current_timestamp ) engine=innodb;  create table if not exists `trdetails3` (   `idevent` bigint(20) not null auto_increment primary key,   `idlite` bigint(20) not null,   `udidate` date default null,   `uditype` varchar(50) default null,   `udiresult` varchar(200) default null,   `supervisor` varchar(50) default null,   `groupin` varchar(50) default null,   `rorder` int(5) default null,   `updated` timestamp not null default current_timestamp ) engine=innodb;  create table if not exists `trdetails4` (   `idevent` bigint(20) not null auto_increment primary key,   `idlite` bigint(20) not null,   `typet` varchar(50) default null,   `number` int(10) default null,   `supervisor` varchar(50) default null,   `pubdate` date default null,   `udidate` date default null,   `uditype` varchar(50) default null,   `result` varchar(50) default null,   `updated` timestamp not null default current_timestamp ) engine=innodb;  create table if not exists `trdetails5` (   `idevent` bigint(20) not null auto_increment primary key,   `idlite` bigint(20) not null,   `typet` varchar(50) default null,   `number` int(10) default null,   `supervisor` varchar(50) default null,   `pubdate` date default null,   `result` varchar(50) default null,   `updated` timestamp not null default current_timestamp ) engine=innodb;  create table if not exists `trlite` (   `idlite` bigint(20) not null auto_increment primary key,   `region` char(30) not null,   `idoffice` smallint(4) not null,   `rifnum` int(11) not null,   `year` mediumint(4) not null,   `sub` tinyint(2) default null,   `section` tinyint(2) not null,   `indate` date not null,   `type` varchar(50) not null,   `subject` varchar(200) not null,   `dcu_instance` char(1) default null,   `dc_instance` char(1) default null,   `defined` tinyint(2) default null,   `defineddate` date default null,   `updated` timestamp null default current_timestamp,   `scroll_table` set  ('00001','00010','00011','00100','00101','00110','00111','01000','01001','01010','01011','01100','01101  ','01110','01111','10000','10001','10010','10011','10100','10101','10110','10111','11000','11001','1101  0','11011','11100','11101','11110','11111') default null ) engine=innodb;  create table if not exists `truserlite` (   `iduser` bigint(20) not null,   `idlite` bigint(20) not null,   `idliteuser` varchar(150) default null,   `status` tinyint(5) not null default '0',   primary key (`iduser`,`idlite`),   key `idlite` (`idlite`) ) engine=innodb;  create table if not exists `tr` (   `idoffice` smallint(4) not null primary key,   `idregion` tinyint(2) not null,   `office` char(200) not null,   `idweboffice` varchar(30) not null ) engine=innodb;  create table if not exists `trinfo` (   `idinfo` bigint(20) not null auto_increment primary key,   `iduser` bigint(20) not null,   `idlite` bigint(20) not null,   `idtable` tinyint(1) not null,   `idevent` bigint(20) not null,   `infodate` timestamp not null default current_timestamp ) engine=innodb;  create table if not exists `users` (   `iduser` bigint(20) not null auto_increment primary key,   `user` varchar(50) not null,   `status` tinyint(5) not null default '0' ) engine=innodb;  alter table `truserlite` add constraint `truserlite_ibfk_1` foreign key (`iduser`) references `users` (`iduser`), add constraint `truserlite_ibfk_2` foreign key (`idlite`) references `trlite` (`idlite`); 

edit 3: (db inserting data criteria)
each idlite can have many details (on 1 or more of 5 detail tables).
when happens insert 1 (or more) detail table new record.
after that, each user needs know (only) new details.
keep known details trinfo , give each user new details (referring idlite linked user).
need extract details not known.

edit 4: (how modify db inserting criteria)
if understand tips, suggesting insert trinfo before detail known, , add field check if relative user has known it. i'll have details trinfo , i'll cut off ones known. way you're telling me do?

wow... had scratch head on one, , see why nobody else tried on it. more confusing simple query / aggregates. anyhow, let me see if understand appears flow of these tables. entry added 1 of detail tables 1-5. these assigned users. based on task @ hand, when completed, record inserted trinfo closing event. reason, starting each detail table , applying not in query trinfo table

now, said, looking transactions associated given user. here, can start directly truserlite table.

from truserlite table, can join respective details tables 1-5 on idlite id, while preserving idevent detail table , corresponding 1-5 value event came from. finally, can left-join trinfo table same user, event , table id #. doing left-join faster not in subselect. eliminate records "completed" including in clause

     , trinfo.idevent null 

now, above being said, , if accurate, following query should out. may have minor syntax issues able resolve (alias name, field, etc).

now i'll explain query. simplify joins of original query, starting truserlite , grabbing entries qualify without additional join trlite , tr tables. joining / unioning them details , trinfo records qualify. while joined details table, respective concatenated fields common "dett" result column. once qualified records complete, join trlite , tr pull additional data final query. keeps inner query tighter limited details, pull rest.

recommended indexes table          index truserlite     ( iduser, status )     optimize clause. trinfo         ( iduser, idlite, idevent, idtable )  select        tr.office,       trl.`year`,       trl.rifnum,       all5.idtable       all5.idlite,       all5.idliteuser,       all5.idevent,       all5.iduser,       all5.dett,       u.user,       u.status userstatus,       concat_ws('_',           ifnull( trl.section,''),           ifnull( trl.indate,''),           ifnull( trl.type,''),           ifnull( trl.subject,''),           ifnull( trl.dcu_instance,''),           ifnull( trl.dc_instance,'') ) essential           ( select                trul.iduser,               trul.idlite,               trul.idliteuser,               1 idtable,               td1.idevent,               concat_ws('_',                  ifnull( td1.typet,''),                   ifnull( td1.in_date,''),                   ifnull( td1.out_date,'') ) dett                          truserlite trul                  join trdetails1 td1                     on trul.idlite = td1.idlite                     left join trinfo                        on trul.iduser = trinfo.iduser                        , trul.idlite = trinfo.idlite                        , td1.idevent = trinfo.idevent                        , trinfo.idtable = 1                              trul.iduser = '@myvarinsertedeachtime'              , trul.status = 0              , trinfo.idevent null          union         select                trul.iduser,               trul.idlite,               trul.idliteuser,               2 idtable,               td2.idevent,               concat_ws( '_',                   ifnull( td2.doctype,''),                   ifnull( td2.number,''),                   ifnull( td2.side,''),                   ifnull( td2.indate,'') ) dett                          truserlite trul                  join trdetails2 td2                     on trul.idlite = td2.idlite                     left join trinfo                        on trul.iduser = trinfo.iduser                        , trul.idlite = trinfo.idlite                        , td2.idevent = trinfo.idevent                        , trinfo.idtable = 2                              trul.iduser = '@myvarinsertedeachtime'              , trul.status = 0              , trinfo.idevent null          union         select                trul.iduser,               trul.idlite,               trul.idliteuser,               3 idtable,               td3.idevent,               concat_ws('_',                   ifnull( td3.udidate,''),                   ifnull( td3.uditype,''),                   ifnull( td3.udiresult,''),                   ifnull( td3.supervisor,''),                   ifnull( td3.groupin,''),                   ifnull( td3.rorder,'') ) dett                          truserlite trul                  join trdetails3 td3                     on trul.idlite = td3.idlite                     left join trinfo                        on trul.iduser = trinfo.iduser                        , trul.idlite = trinfo.idlite                        , td3.idevent = trinfo.idevent                        , trinfo.idtable = 3                              trul.iduser = '@myvarinsertedeachtime'              , trul.status = 0              , trinfo.idevent null          union         select                trul.iduser,               trul.idlite,               trul.idliteuser,               4 idtable,               td4.idevent,               concat_ws('_',                   ifnull( td4.type,''),                   ifnull( td4.number,''),                   ifnull( td4.supervisor,''),                   ifnull( td4.pubdate,''),                   ifnull( td4.udidate,''),                   ifnull( td4.uditype,''),                   ifnull( td4.result,'') ) dett                           truserlite trul                  join trdetails4 td4                     on trul.idlite = td4.idlite                     left join trinfo                        on trul.iduser = trinfo.iduser                        , trul.idlite = trinfo.idlite                        , td4.idevent = trinfo.idevent                        , trinfo.idtable = 4                              trul.iduser = '@myvarinsertedeachtime'              , trul.status = 0              , trinfo.idevent null          union         select                trul.iduser,               trul.idlite,               trul.idliteuser,               5 idtable,               td5.idevent,               concat_ws('_',                   ifnull( td5.type,''),                   ifnull( td5.number,''),                   ifnull( td5.supervisor,''),                   ifnull( td5.pubdate,''),                   ifnull( td5.result,'') ) dett                          truserlite trul                  join trdetails5 td5                     on trul.idlite = td5.idlite                     left join trinfo                        on trul.iduser = trinfo.iduser                        , trul.idlite = trinfo.idlite                        , td5.idevent = trinfo.idevent                        , trinfo.idtable = 5                              trul.iduser = '@myvarinsertedeachtime'              , trul.status = 0              , trinfo.idevent null ) all5           join users u             on all5.iduser = u.iduser           join trlite trl             on all5.idlite = trl.idlite             join tr                on trl.idoffice = tr.idoffice    order       tr.office,       trl.`year`,       trl.rifnum,       all5.idtable 

(btw, added user table join in case wanted columns well... can removed)

it interesting know performance time of original query result of one, if not resolve query time.

feedback

you looking things not in trinfo table. if possibly restructure add status column trinfo such "isrecordopen" (exaggerating column name) , have set 1, , @ time, insert trdetails table. when completed, set flag 0 (no longer open task). basis of each item pending work completed , points respective detail table event id. query simplified down like..

select          tr.office,           trl.`year`,           trl.rifnum,           tri.idtable           tri.idlite,           trul.idliteuser,           tri.idevent,           tri.iduser,           case when tri.idtable = 1                   concat_ws('_',                      ifnull( td1.typet,''),                       ifnull( td1.in_date,''),                       ifnull( td1.out_date,'') )                 when tri.idtable = 2                   concat_ws( '_',                       ifnull( td2.doctype,''),                       ifnull( td2.number,''),                       ifnull( td2.side,''),                       ifnull( td2.indate,'') )                  when tri.idtable = 3                   concat_ws('_',                       ifnull( td3.udidate,''),                       ifnull( td3.uditype,''),                       ifnull( td3.udiresult,''),                       ifnull( td3.supervisor,''),                       ifnull( td3.groupin,''),                       ifnull( td3.rorder,'') )                  when tri.idtable = 4                   concat_ws('_',                       ifnull( td4.type,''),                       ifnull( td4.number,''),                       ifnull( td4.supervisor,''),                       ifnull( td4.pubdate,''),                       ifnull( td4.udidate,''),                       ifnull( td4.uditype,''),                       ifnull( td4.result,'') )                 when tri.idtable = 5                   concat_ws('_',                       ifnull( td5.type,''),                       ifnull( td5.number,''),                       ifnull( td5.supervisor,''),                       ifnull( td5.pubdate,''),                       ifnull( td5.result,'') )                  end dett,            concat_ws('_',               ifnull( trl.section,''),               ifnull( trl.indate,''),               ifnull( trl.type,''),               ifnull( trl.subject,''),               ifnull( trl.dcu_instance,''),               ifnull( trl.dc_instance,'') ) essential          trinfo tri          join trlite trl             on tri.idlite = trl.idlite             join tr                on trl.idoffice = tr.idoffice           join truserlite trul             on tri.iduser = trul.iduser            , tri.idlite = trul.idlite            , trul.status = 0           left join trdetails1 td1             on tri.tableid = 1            , tri.idevent = td1.idevent          left join trdetails2 td2             on tri.tableid = 2            , tri.idevent = td2.idevent          left join trdetails1 td3             on tri.tableid = 3            , tri.idevent = td3.idevent          left join trdetails1 td4             on tri.tableid = 4            , tri.idevent = td4.idevent          left join trdetails1 td5             on tri.tableid = 5            , tri.idevent = td5.idevent              tri.iduser = 'the user id want'       , tri.isrecordopen