sql - MySQL: How can this UNION be improved? -

sql - MySQL: How can this UNION be improved? -

i have 2 tables, access , p provider. have 3rd table joining tables together, standard normalization. however, provider table parent/child table, , joining table has alternative whether access should granted provider children or not.

create table p ( p_id int primary key, name varchar(32), parent_id int, foreign key (parent_id) references p(p_id) ); create table ( a_id int primary key, name varchar(32) ); create table ap ( a_id int, p_id int, sub tinyint, foreign key (a_id) references a(a_id), foreign key (p_id) references p(p_id) );

some sample data, 1 provider 2 kid providers. 2 access users, 1 no kid access , kid access.

insert p values(1, 'a', null); insert p values(2, 'a.a', 1); insert p values(3, 'a.b', 1); insert values(1, 'user 1'); insert values(2, 'user 2'); insert ap values(1, 1, 0); insert ap values(2, 1, 1);

the result want have list of providers user have access to, based on 3rd table.

currently i've solved joining 2 queries union. first query selects possible kid providers , sec goes primary providers.

select p_id, name p parent_id in( select p_id ap a_id = 1 , sub = 1 ) union select ap.p_id, p.name ap left bring together p on p.p_id = ap.p_id a_id = 1;

i don't query, it's ugly , there must smarter way :)

if have logic correct, want records p 1 of next true:

p_id matches record in ap given a_id. parent_id matches record in ap given a_id.

this suggests using exists conditions:

select p.p_id, p.name p exists (select 1 ap ap.p_id = p.p_id , ap.a_id = 1 ) or exists (select 1 ap ap.p_id = p.parent_id , ap.sub = 1 , ap.a_id = 1 )

with composite index on ap(p_id, a_id, sub), should have much improve performance version of query.

mysql sql