Will indexing be useful when Inner join is performed between normal table and a select from indexed table

Question

Suppose I have small table(t1) and large table(t2).I have indexed column1 and column2 of t2. If I want to INNER JOIN t1 and (select * from t2 where column1=x) then is the indexing on t2 be helpful even after the (select * from t2 where column1=x) during the inner join with t1?

If My query is (select * from t2 where column1=x) then obviously indexing is helpful. What happens when my complete query is run? will it first run (select * from t2 where column1=x)(here indexing will be used) and then INNER JOIN with t1 without using indexing?

Answer 1

Almost always it is better to JOIN two tables instead of JOINing to a "derived" table.

Probably inefficient:

 FROM t1
 JOIN ( SELECT ... FROM t2 ... ) AS t3 ON ...

Probably better:

 FROM t1
 JOIN t2 ON ...

One likely exception is when the derived table (t3) is much smaller than the table (t2) it comes from. This may happen when there is a GROUP BY, DISTINCT, and/or LIMIT inside t3.

If you want to discuss further, please provide the fully spelled out SELECT and SHOW CREATE TABLE for the two tables. An important discussion point is what indexes exist (or are missing).