I was trying to optimize my SQL query and I encountered behavior that I can't understand. 

Note: If I'm using JOIN also it examines all the records of table2

Version - 5.6 

**QUERY 1 (Slow)**

```
-- Joining 2 table using subqueries. 
-- Even If I'm using JOIN also it examines all the records of table2
SELECT 
    *
FROM
    table2
WHERE
    table1_id IN (SELECT 
            id
        FROM
            table1
        WHERE
            idx_column = 'value');
-- taking more than 20 seconds

```

Explain 

```
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+------------------------------------------------------------------+
|id |select_type|table          | type | possible_keys                     | key      | key_len | ref           | rows  | Extra                                                           |
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+------------------------------------------------------------------+
|1  |SIMPLE     |table1         |ref   |PRIMARY,idx_column_idx             | PRIMARY  |4        |               |2      |Using index condition; Start temporary                           |
|1  |SIMPLE     |table2         |ALL   |NULL                               | NULL     | NULL    | NULL          |1129137|Using where; End temporary; Using join buffer (Block Nested Loop)|
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+------------------------------------------------------------------+ 

```

**QUERY 2 (Fast)**

```
-- Separately run the first subquery.
SELECT 
    id
FROM
    table1
WHERE
    idx_column = 'value';

--- 
SELECT 
    *
FROM
    table2
WHERE
    table1_id IN ({{Selected Id's from above query}}); 
-- taking less than a second

```

Explain 

```
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+------------------------------------------------------------------+
|id |select_type|table          | type | possible_keys                     | key      | key_len | ref           | rows  | Extra                                                           |
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+------------------------------------------------------------------+
|1  |SIMPLE     |table2         |ref   |PRIMARY,table1_id                  | table1_id|48       | const         |2      |Using index condition                                            |
+---+-----------+---------------+------+-----------------------------------+----------+---------+---------------+------+------------------------------------------------------------------+ 

```

Explain @scaisedge's Query

```
+---+------------+--------------------+------+---------------+----------------+---------+------+-------+---------------------------------------------------+
|id |select_type | table              | type | possible_keys | key            | key_len |  ref | rows  | Extra                                             |
+---+------------+--------------------+------+---------------+----------------+---------+------+-------+---------------------------------------------------+
|1  |PRIMARY     |<derived2>          |ALL   |NULL           | NULL           |NULL     |NULL  |2      |NULL                                               |
|1  |PRIMARY     |table2              |ALL   |NULL           | NULL           |NULL     |NULL  |1129137|Using where; Using join buffer (Block Nested Loop) |    
|2  |DERIVED     |table1              |ref   |idx_column_IDX | idx_column_IDX | 33      | const|2      |Using index condition                              |
+---+------------+--------------------+------+---------------+----------------+---------+------+-------+---------------------------------------------------+ 

```