Reuse mysql Subquery in InnerJoin

Question

I'm trying optimizing a query, trying to avoid repeating the query indicated with "COMPLEX QUERY", that is used 2 times and both, has the same results.

The original query

SELECT news.* 
FROM   news 
   INNER JOIN((SELECT myposter 
               FROM   (SELECT **COMPLEX QUERY**)) 
              UNION 
              (SELECT myposter 
               FROM   `profiles_old` prof2 
               WHERE  prof2.profile_id NOT IN (SELECT **COMPLEX QUERY**))) r 
           ON news.profile = r.p

I was wondering if something like this was possible:

SELECT news.* 
FROM   (SELECT **COMPLEX QUERY**) complexQuery, 
   news 
   INNER JOIN ((SELECT myposter 
                FROM   complexquery) 
               UNION 
               (SELECT myposter 
                FROM   `profiles_old` prof2 
                WHERE  prof2. profile NOT IN (SELECT myposter 
                                              FROM complexQuery))) r 
           ON news. profile = r.p

Does Mysql do some sort of caching of that type of query?

Hmm i tryed to have a look but I guess I'd need the actual subquery to tell what's the best solution. — Sebas, Nov 07 '13 at 00:47
Also, it would be useful if you provide tables structures and EXPLAIN results of your queries. — Konstantin Kiselev, Nov 07 '13 at 01:50
The problem is not how to implement the complex query, but how to reuse its values: in the first part I fetch all the user I can from the complex query, in the second, I try to fetch all the users I didn't fetch before, from an old table that contains also values that can't be retrieved from **complex query** — M4rk, Nov 07 '13 at 10:36

score 5 · Answer 1 · answered Nov 07 '13 at 02:26

The direct answer to your question is "no". MySQL does not support what you want. What you really want is the with statement. Great statement! If you want it, use a different database. Alas.

I do think you can do this, although the approach is quite different from what you are doing.

The logic is to take all values of myposter from the complex query and to take all values of myposter from profiles_old where the corresponding profile_id is not in the complex query.

You can do this with union all and aggregation. Just focusing on the inner subquery:

select (case when max(which = 'cq') = 1 then myposter
             when max(which = 'po') = 1 and max(which = 'cq') = 0 then id2
       ) as myposter
from (select myposter, myposter as id2, 'cq' as which
      from (select **complex query**) cq
      union all
      select profile_id, myposter, 'po' as which
      from profiles_old
     ) t
group by myposter;

The rest is just incorporating this into your overall query.

score 0 · Answer 2 · answered May 12 '14 at 09:40

0

I have gone through the requirements, giving suggestion in different approach:

/* Create temporary table (Preferred to use memory storage engine though it's not mandatory): */

MYSQL> create temporary table tmp_intermediate_table engine=memory select COMPLEX QUERY;

Then, refer tmp_intermediate_table tables in your query which runs in the current user session.

answered May 12 '14 at 09:40

Suresh Gautam

816
8
21

This won't work as it will require reusing the same temporary table twice in the same query which means reopening the same table. This isn't allowed in mysql – pankajagarwal Apr 03 '18 at 13:22
@pankajagarwal, I think you didn't get my point, reusing temporary table with the same complex query can be used as many times as required without creating multiple times. Also, you can create separate temporary table if you have different complex query once and reuse them multiple times. – Suresh Gautam Apr 10 '18 at 07:47
@pankajagarwal - Refer following reference to check how MYSQL allow to create temporary table: https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table – Suresh Gautam Apr 12 '18 at 03:52

Reuse mysql Subquery in InnerJoin

2 Answers2

Linked