Index On Join And Where

Given the next SQL statement: Select * from A join B on A.id1=B.id1 and A.id2=B.id2 where A.year=2016 and B.year=2016 and knowing table A is much

Solution 1:

For this query:

Select *
from A join
     B
     on A.id1 = B.id1 and A.id2 = B.id2
where A.year = 2016and B.year = 2016;

I would suggest indexes on A(year, id1, id2) and B(id1, id2, year).

You might also write the query as:

Select *
from A join
     B
     on A.id1 = B.id1 and A.id2 = B.id2 and A.year = B.year
where A.year = 2016;

The answer to your question is "yes" and index on B is the right thing to do. In this version, the order of the columns in the index does not really matter.