CODEWITHSUNDEEP
mysqloptimization
Adi
Adi

Reputation: 5169

Using subquery result in WHERE IN in another subqueries (optimization)

This has been bugging me for some time now, as a newbie I'm not sure what I should exactly look for. After several hours of Googling I wasn't able to find a satisfying answer.

I have a relatively complex query which I have stripped and simplified to become:

SELECT field_a1,
       field_a2,
       (SELECT Count(*)
        FROM   table_b
        WHERE  field_b1 IN (SELECT field_x1
                            FROM   table_x) AND field_b2 = 1) AS new_field_1,
       (SELECT Count(*)
        FROM   table_c
        WHERE  field_c1 IN (SELECT field_x1
                            FROM   table_x) AND field_c2 = 2) AS new_field_2,
       (SELECT Count(*)
        FROM   table_d
        WHERE  field_d1 IN (SELECT field_x1
                            FROM   table_x) AND field_d2 = 3) AS new_field_3
FROM   table_a
WHERE  field_a1 IN (SELECT field_x1
                    FROM   table_x)

In the original query, I have a about 30 of those new_field_*. Everything seems to work fine and I'm getting some very satisfying results (end results and performance).

My problem is that this query:

SELECT field_x1
FROM   table_x

is repeated a quite large number of times (30 * number of rows returned) and is always used as a set in which the WHERE IN will look for matches.

My question:

Is it possible to execute this small query only once and keep that result and use it repeatedly?

The only two (ridiculous) things I could think of were something like this (imaginary solution):

temp = (SELECT field_x1
        FROM   table_x)
SELECT field_1
FROM   table_1
WHERE  field_1 IN temp

Or executing this query once on the client side (a webserver running PHP) then appending the result to the future queries using something like this:

$IN_Condition = implode(',', $stmt->execute()->fetchAll(PDO::FETCH_NUM));
$sql_query = "SELECT field_1
              FROM   table_1
              WHERE  field_1 IN ($IN_Condition)"

Upvotes: 0

Views: 146

Answers (2)

Elias Platek
Elias Platek

Reputation: 1114

Sometimes you don't need to do optimizations on the query, when the engine sees similar subqueries it fetches data only once. But you can try this query and see if it is faster:

SELECT a.field_a1, a.field_a2,
   (SELECT Count(*)
    FROM x
    WHERE x.field_x1 = b.field_b1
      AND b.field_b2 = 1) AS new_field_1,
   ...
FROM table_a, table_b b, ..., -- Query here the tables you need
     (SELECT field_x1
      FROM table_x) x
WHERE field_a1 IN x

You can also keep in memory your table, and compare data with your php engine, but it will be slower than working with the db engine.

EDIT: Each table you need will be queried once

EDIT 2: Reverse the logic!

Upvotes: 1

Devart
Devart

Reputation: 121902

Try to use JOIN clauses, e.g. -

edited:

SELECT
  field_a1,
  COUNT(b.field_b1) new_field_1,
  COUNT(c.field_c1) new_field_2,
  COUNT(d.field_d1) new_field_3
FROM
  table_a a
JOIN table_x x
  ON x.field_x1 = a.field_a1
LEFT JOIN table_b b
  ON b.field_b1 = x.field_x1
LEFT JOIN table_c c
  ON c.field_c1 = x.field_x1
LEFT JOIN table_d d
  ON d.field_d1 = x.field_x1
GROUP BY
  a.field_a1

Upvotes: 1

Related Questions