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?

why on earth would you give each of your tables the same alias :/ — pala_, Apr 25 '15 at 08:42
@pala_ , it won't be a issue, because those alias are subqueries which will be independent of other subqueries and main query.. TBH, I've not given those aliases, those aliases are generated by Hibernate — The Coder, Apr 25 '15 at 08:43
i know it's not an issue for execution, just for bloody readability. anyway, [here](http://stackoverflow.com/questions/29859808/why-does-a-query-with-an-in-subquery-take-longer-than-a-query-with-in-discret) is a link that may help explain why the subqueries won't scale well. — pala_, Apr 25 '15 at 08:44

score 1 · Accepted Answer · answered Apr 25 '15 at 08:45

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

answered Apr 25 '15 at 08:45

M Sach

33,416
76
221
314

So, I believe mine's is a correlated subquery which will result in poor performance. If I've to make it as a Subquery, I've to use joins right? – The Coder Apr 25 '15 at 08:49
one way is join and another way is sub query/inner query(not the correlated subquery) – M Sach Apr 25 '15 at 08:50
Well then looks like I've seperate it to individual queries to make it just as a SubQuery, thanks.. – The Coder Apr 25 '15 at 08:55
But I don't understand the fact why the Correlated Subqueries are executed like that, I mean why the MySQL devs have designed it like that.. – The Coder Apr 25 '15 at 08:57
As you are referring outer table in inner query, Its logical. – M Sach Apr 25 '15 at 08:58

Splitting Subqueries into Seperate Queries is more efficient?

1 Answers1