Passing the result from one subquery to an IN clause in another subquery in MySQL

Not sure if this is possible, but if it is it would make my query much faster.

Basically I have a query like this:

 SELECT *
   FROM (SELECT bar.id
           FROM pivot_table
          WHERE foo.id = x) t1
   JOIN (SELECT count(*) c1, bar.id
           FROM table
       GROUP BY bar.id) t2 ON t1.id = t2.id
   JOIN (SELECT count(*) c2, bar.id
           FROM another_table
       GROUP BY bar.id) t3 ON t1.id = t3.id

But this is quite slow because table and another_table are huge. But really I am only interested in those values resulting from the query in t1. So if I could somehow get those results into an IN clause for t2 and t3 the query ought to speed up significantly.

Is this possible?

---

Not too clear I guess. OK what I was thinking is that changing the query to something like:

 SELECT *
   FROM (GROUP_CONCAT (bar.id) as results
                 FROM pivot_table
                WHERE foo.id = x) t1
         JOIN (SELECT count(*) c1, bar.id
                 FROM table
                WHERE bar.id IN (*results from t1*)
                GROUP BY bar.id) t2 ON t1.id = t2.id
         JOIN (SELECT count(*) c2, bar.id
                 FROM another_table
                WHERE bar.id IN (*results from t1*)
                GROUP BY bar.id) t3 ON t1.id = t3.id

Might be quicker because it narrows down the number of rows scanned in t2 and t3. Would that not be the case?

---

Everyone wants to see it, so here is the full query:

SELECT   (k_group.count/jk_group.count) * (s_group.count/jk_group.count) AS ratio,
         jk_group.k_id                                                           ,
         jk_group.s_id
FROM
         -- find the keywords for the job
         (SELECT jk.keyowrd_id AS k_id
         FROM    jobs_keywords jk
         WHERE   job_id = 50100
         )
         extracted_keywords
         -- calculate the necessary values using group_by functions
         INNER JOIN
                  (SELECT  COUNT(*)   count,
                           skill_id   AS s_id ,
                           keyword_id AS k_id
                  FROM     jobs_keywords jk
                           JOIN jobs_skills js
                           ON       js.job_id = jk.job_id
                           JOIN job_feed_details d
                           ON       d.job_id = js.job_id
                  WHERE    d.moderated       = 1
                  GROUP BY skill_id,
                           keyword_id
                  )
                  jk_group
         ON       extracted_keywords.k_id = jk_group.k_id
         INNER JOIN
                  (SELECT  COUNT(*)      count,
                           keyword_id AS k_id
                  FROM     jobs_keywords jk
                           JOIN job_feed_details d
                           ON       d.job_id = js.job_id
                  WHERE    d.moderated       = 1
                  GROUP BY keyword_id
                  )
                  k_group
         ON       jk_group.k_id = k_group.k_id
         INNER JOIN
                  (SELECT  COUNT(*)    count,
                           skill_id AS s_id
                  FROM     jobs_skills js
               开发者_如何学Go            JOIN job_feed_details d
                           ON       d.job_id = js.job_id
                  WHERE    d.moderated       = 1
                  GROUP BY skill_id
                  )
                  s_group
         ON       jk_group.s_id = s_group.s_id
ORDER BY ratio DESC
LIMIT    25

---

SELECT COUNT(t1.id) c1, COUNT(t2.id) c2, COUNT(t3.id) c3, t1.id 
FROM pivot_table t1 
JOIN table t2 ON t1.id=t2.id 
JOIN another_table t3 ON t3.id=t1.id where t1.id=x group by t1.id

pls make sure the pivot_table.id, table.id and another_table.id are indexed

about your query: the problem of your query is driverd table use join buffer, to make your query fast, you should increase your join buffer size

---

I was able to accomplish what I was trying to do like so:

 SELECT *
   FROM (@var:=GROUP_CONCAT(bar.id) as results
                 FROM pivot_table
                WHERE foo.id = x) t1
         JOIN (SELECT count(*) c1, bar.id
                 FROM table
                WHERE bar.id IN (@var)
                GROUP BY bar.id) t2 ON t1.id = t2.id
         JOIN (SELECT count(*) c2, bar.id
                 FROM another_table
                WHERE bar.id IN (@var)
                GROUP BY bar.id) t3 ON t1.id = t3.id

But the benefits in terms of speed were not too significant. I have now abandoned the one query approach in favor of many smaller queries, and that is much better.

---

Revision given actual query

I think you can whittle your query down to:

Select jk.Count( Distinct jk.keyword_id )
        * jk.Count( Distinct js.skill_id )
        / Power( Count(*), 2 )
        As ratio
    , js.skill_id
    , jk.keyword_id
From jobs_keywords As jk
    Join jobs_skills As js
        On js.job_id = jk.job_id
Where jk.job_id =50100
Group By js.skill_id, jk.keyword_id
Order By ratio Desc
Limit 25