CODEWITHSUNDEEP
mysql
Code
Code

Reputation: 6251

Is multiple queries or LEFT JOIN faster?

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 rows in tb1 where col1 = 123,

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

I could 2 selects:

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 a 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?

Upvotes: 4

Views: 94

Answers (1)

Amit Shah
Amit Shah

Reputation: 1380

Joins are faster, please use EXPLAIN keyword before query start, you will come to know the statistics in terms of time and other factor of how mysql executes query against database.

e.g.

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

Upvotes: 3

Related Questions