Q:

Inner Join with or condition

I have two table as follows
1. Table tb1
===============================
   id   name   originator
   1    xyz    40
   2    abc    40
   3    xyz1   50
   4    abc1   60

2. Table tb2
=======================================
   id_fk    partyId   permission
    1       40        admin
    1       41        user
    2       43        user
    3       40        admin
    3       67        user
    3       88        admin
    4       40        admin
    4       51        user

I need to write a query which will bring ids whose originator either 40 or partyId is 40

select id from tb1 t1,tb2 t2 where t1.id=t2.id_fk and (t1.originator = 40 or t1.id in (select id_fk from tb2 where partyId=40))
select id from tb1 t1,tb2 t2 where t1.id=t2.id_fk and (t1.originator = 40 or t2.partyId=50)

but the both above queries taking lot of time over 2 millions records. 
Can anyone help me to optimize this query.

A:

You would be best to handle this with a union query so the index can be optimized on each portion respectively
select DISTINCT
      tb1.id
   from
      tb1
   where
      tb1.originator = 40
UNION
select 
      tb2.id_fk as id
   from
      tb2
   where
      tb2.PartyID = 40

I would have the following indexes on your tables
Table    Index
TB1      (Originator, id)
TB2      (PartyID, id_fk)

The select distinct will return only one record for any given ID no matter how many records qualify such as multiple detail records in the TB2 for a given person.  But also those in TB1 based on the originator.
If you then want the names for those people, you would wrap that to get names based on the result IDs.
You mention an order by and a limit, in your direct comment to me, but no clarification.  What is the basis of the ordering.  Are you trying to get name?  And also the limit...  Please provide more by editing original post for others as well.  However, I would only adjust via
select
      PQ.*
   from
      ( entire query above ) PQ
   order by
      PQ.ID
   limit
      0,15

