How optimized are sub-queries in MySQL?
My question is long but simple. I have queries of the following kind:

    select obj from table where condition1 and obj in (
    select obj from table where condition2 and obj in (
    select obj from table where condition3 and obj in (
    ...
    )))

This query takes a very long time. I am afraid that MySQL performs this query in the following way (for simplicity let's consider just one sub-query):

    select obj from table1 where condition1 and obj in (select obj from table2 where condition2)

The MySQL goes through rows of the table 1. If `condition1` is fulfilled it starts a loop over the object from the set obtained by the sub-query. If there is a considered object, it is added to the "final" list of objects.

My problem is that the above example has a loop in a loop. And if I have two sub-queries I have a loop in a loop in a loop. If every loop has about 10 000 cycles, I have a disaster.

My solution is to find n sets (every set corresponds to one `select`). Then I make a loop over every set independently (so, I do not have loops in loops). Something like that.

    dictionary = []
    for (elem in set1) {
       dictionary[elem] += 1
    }
    for (elem in set2) {
       dictionary[elem] += 1
    }
    for (elem in set3) {
       dictionary[elem] += 1
    }

Then I make one more loop over all elements of the `dictionary` and select only those keys that were found in all sets:

    finalSet = []
    for (elem in dictionary) {
       if (dictionary[elem]==numberOfSets) {
          finalSet.add(elem)
       }
    }

So, my question is MySQL is optimized in a way I want it to be optimized (above described way). Or it produce loops in loops in loops....


How optimized are sub-queries in MySQL?
===

My question is long but simple. I have queries of the following kind:

    select obj from table where condition1 and obj in (
    select obj from table where condition2 and obj in (
    select obj from table where condition3 and obj in (
    ...
    )))

This query takes a very long time. I am afraid that MySQL performs this query in the following way (for simplicity let's consider just one sub-query):

    select obj from table1 where condition1 and obj in (select obj from table2 where condition2)

The MySQL goes through rows of the table 1. If `condition1` is fulfilled it starts a loop over the object from the set obtained by the sub-query. If there is a considered object, it is added to the "final" list of objects.

My problem is that the above example has a loop in a loop. And if I have two sub-queries I have a loop in a loop in a loop. If every loop has about 10 000 cycles, I have a disaster.

My solution is to find n sets (every set corresponds to one `select`). Then I make a loop over every set independently (so, I do not have loops in loops). Something like that.

    dictionary = []
    for (elem in set1) {
       dictionary[elem] += 1
    }
    for (elem in set2) {
       dictionary[elem] += 1
    }
    for (elem in set3) {
       dictionary[elem] += 1
    }

Then I make one more loop over all elements of the `dictionary` and select only those keys that were found in all sets:

    finalSet = []
    for (elem in dictionary) {
       if (dictionary[elem]==numberOfSets) {
          finalSet.add(elem)
       }
    }

So, my question is MySQL is optimized in a way I want it to be optimized (above described way). Or it produce loops in loops in loops....

