Splitting Subqueries into Seperate Queries is more efficient?

Question

I've a subquery like this. Basically I've used multiple detached criteria to form multiple subqueries.

SELECT
        this_.id AS y0_,
        this_.a AS y1_,
        this_.b AS y2_,
        this_.c AS y3_,
        this_.d AS y4_,
        this_.e AS y5_ 
    FROM
        table_a this_ 
    WHERE
        this_.id IN (
            SELECT
                this_.a AS y0_ 
            FROM
                table_b this_ 
            WHERE
                this_.b=?
                )

Currently I don't have large amount of data in my database tables. It takes less than 0.01 seconds to execute the query. But now I've been informed to make each subquery as a seperate query to feed the list of values to each IN statement. I don't know what'll be difference between the informed strategy vs current subqueries (posted above). Subquery also returns a list of id's to its IN statement, list of ids will also be feed to the IN statement if used sepearate queries? How come both these strategies differ in performance.

I've been told that my current subquery will become slow if there are large data in tables. Both the strategies are doing the same function. So Why it'll result in slow performance for subqueries?

Just consider a sample Java method calling

public static void main(String[] args){
    System.out.print( c( b( a() ) ) );
}

public String a(){
    return "success";
}
public String b(String string){
    return string;
}
public String c(String string){
    return string;
}

First the a will be called, then b will be called which will be feed with return of a, finally c will be called which will be feed with return of b. Then the return of c will be displayed.

I assume the same kind of functionality is also followed in the MySQL subqueries. Am I right? SO why Subqueries will result in slow performance when comparing with total time of all seperate queries?

Answer 1

i believe you are asking whats the difference b/w correlated sub query and sub query and its performance aspect

Subquery :- The inner query is executed only once The inner query will get executed first and the output of the inner query used by the outer query

Correlated subquery:- The outer query will get executed first and for every row of outer query, inner query will get executed. So the inner query will get executed as many times as no.of rows in result of the outer query.

Using Co-related sub-query performance decreases, since, it performs NXM iterations