The optimization process and recommendations:

1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
2. Avoid Subqueries (query line: 4): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
3. Avoid Subqueries (query line: 19): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX table_1_idx_col_2_col_1_col_3 ON TABLE_1 (COL_2,COL_1,COL_3);
CREATE INDEX table_2_idx_col_a_col_b ON TABLE_2 (COL_A,COL_B);

The optimized query:

SELECT
        * 
    FROM
        (SELECT
            TABLE_1.COL_1,
            TABLE_1.COL_2,
            TABLE_1.COL_3 
        FROM
            TABLE_1 
        WHERE
            TABLE_1.COL_1 IN (
                'item1', 'item2'
            ) 
            AND TABLE_1.COL_2 = 100 
        ORDER BY
            TABLE_1.COL_3) subquery1 
    INNER JOIN
        (
            SELECT
                TABLE_2.COL_A,
                MAX(TABLE_2.COL_B) 
            FROM
                TABLE_2 
            GROUP BY
                TABLE_2.COL_A 
            HAVING
                COUNT(TABLE_2.COL_B) > 2
        ) subquery2 
            ON subquery1.COL_1 = subquery2.COL_A