mysql - Optimizing Related Table Query -

i have users table user info , related table show related users current user.

to related users user id '25' query looks like

select   id users u   inner join (select                 primary_id,                 secondary_id               users_rel               primary_id = '25'                    or secondary_id = '25') temp     on (u.id = temp.primary_id          or u.id = temp.secondary_id) u.id != '25' 

the issue here in users_rel table user id can either on primary side or on secondary side. don't tell me change because done 6 million records can not change it. query takes 2 5 mins execute 4000 records in user_rel table , 629241 in users table.

    user_rel table .--------------------------------. |    id         |  (varchar,36 ) |    |   primary_id  |  (varchar,36)  | |  secondary_id |  (varchar,36)  | |    del        |  (tinyint,1)   | |.______________________________.| 

and index defined combination of primary_id , secondary_id

its done 6 million records can not change it

grow pair hardcore - you've got relatively small amount of data in tables no reason not fixing bad design.

it if posted exact structure (i.e. create table statements) both tables , explain plan.

why using sub query instead of joining table? starting again gives can begin optimize:

select u.id users u inner join users_rel r     on 25 in (primary_id, secondary_id)     , (u.id = r.primary_id          or u.id=r.secondary_id) u.id != '25'; 

this should give same result. whether it's faster....we don't know.

index defined combination of primary_id , secondary_id

why? it's not going query - need 2 indexes 1 on primary_id 1 on secondary_id.