Is multiple queries or left joins faster?

Question

I have 2 tables:

TABLE tb1
id int(11)
col1 int(11)
PRIMARY (id)

TABLE tb2
id int(11)
col2 int(11)
tb1_id int(11)
PRIMARY (id)
INDEX (tb1_id)
UNIQUE (col2, tb1_id)

I am trying to get the strings in tb1, where col1 = 123,

and then check if a unique pair exists [col2=456, tb1_id=<tb1 id>]in tb2.

---

I could choose 2:

SELECT tb1.*
FRoM tb1
WHERE tb1.col1 = 123

foreach <tb1 id> in results
    SELECT COUNT(*)
    FROM tb2
    WHERE tb2.col2 = 456 AND tb2.tb1_id = <tb1 id>

Or use LEFT JOIN

SELECT tb1.*, COUNT(tb2.id)
FROM tb1
LEFT JOIN tb2 ON tb2.tb1_id = tb1.id
WHERE tb1.col1 = 123 AND tb2.col2 = 456
GROUP BY tb1.id

which is faster?

Answer 1

Connections are faster, use the EXPLAIN keyword before starting the query, you will find out statistics in terms of time and another factor in how mysql executes the query to the database.

eg.

EXPLAIN SELECT tb1.*, COUNT(tb2.id)
FROM tb1
LEFT JOIN tb2 ON tb2.tb1_id = tb1.id
WHERE tb1.col1 = 123 AND tb2.col2 = 456
GROUP BY tb1.id

Thanks Amit