MySQL multiple sub-queries optimization

Question

I have 3 tables
Table1 has id cell
Table2 has id cell
Table3 has id1 cell -> corresponds to Table1.id
Table3 has id2 cell -> corresponds to Table2.id

I need a result that count how many times the pair of id1 and id2 appeared in Table3, also for a result that did not appear.

So cant use simple Select * from table3 group by id1, id2.

I used

SELECT *
FROM table1, table2, 
    (SELECT COUNT(*) AS count
    FROM table3
    GROUP BY id1, id2)
GROUP BY table1.id, table2.id

but it is slow as molasses and it does not work.

Shidersz · Accepted Answer · 2018-10-31 03:35:23Z

1

Try next query:

1) First, we cross join table1 and table2 for get all combination tuples.

2) Second, we left join to table3.

3) We count the number of rows obtained for each tuple, counting not null values on table3->id1 (This assume that tuples of types <null, id> or <id, null> don't exists on table3)

SELECT
   t1.id, t2.id, COUNT(t3.id1) AS count
FROM
   table1 AS t1
CROSS JOIN
   table2 AS t2
LEFT JOIN
   table3 AS t3 ON t3.id1 = t1.id AND t3.id2 = t2.id
GROUP BY
   t1.id, t2.id

edited Oct 31, 2018 at 3:35

answered Oct 31, 2018 at 3:22

Shidersz

17.2k2 gold badges28 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Milika Over a year ago

Showing rows 0 - 24 (34188 total, Query took 13.3809 seconds.) So not really a fast query, though faster than mine :D

Shidersz Over a year ago

I think you need to add an index by (id1, id2) on table3. How much rows table3, table2 and table1 have?

Milika Over a year ago

I added index, and it seems much faster. t1 - 20rows t2 - 3000rows t3 - 20k rows Will test more

Mureinik · Accepted Answer · 2018-10-31 04:47:21Z

1

I'd cross-join table1 and table2, and then left join this on table3, and count an expression to check if combination exists:

SELECT     t1.id,
           t2.id,
           COUNT(CASE WHEN t3.id1 IS NOT NULL AND t3.id2 IS NOT NULL THEN 1 END)
FROM       t1
CROSS JOIN t2
LEFT JOIN  t3 ON t1.id = t3.id1 AND t2.id = t3.id2
GROUP BY   t1.id, t2.id

edited Oct 31, 2018 at 4:47

answered Oct 31, 2018 at 3:24

Mureinik

316k54 gold badges402 silver badges406 bronze badges

1 Comment

Milika Over a year ago

This is missing GROUP BY t1.id, t2.id but then its slow as anything i tried (14s), see @d-smania answer

Collectives™ on Stack Overflow

MySQL multiple sub-queries optimization

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related