CODEWITHSUNDEEP
sqljoin
mnowotka
mnowotka

Reputation: 17218

SQL joins vs nested query

This two SQL statements return equal results but first one is much slower than the second:

SELECT leading.email, kstatus.name, contacts.status 
FROM clients 
JOIN clients_leading ON clients.id_client = clients_leading.id_client 
JOIN leading ON clients_leading.id_leading = leading.id_leading 
JOIN contacts ON contacts.id_k_p = clients_leading.id_group 
JOIN kstatus on contacts.status = kstatus.id_kstatus 
WHERE (clients.email = 'some_email' OR clients.email1 = 'some_email') 
ORDER BY contacts.date DESC;



SELECT leading.email, kstatus.name, contacts.status
FROM (
     SELECT * 
     FROM clients 
     WHERE (clients.email = 'some_email' OR clients.email1 = 'some_email')
     ) 
AS clients  
JOIN clients_leading ON clients.id_client = clients_leading.id_client 
JOIN leading ON clients_leading.id_leading = leading.id_leading  
JOIN contacts ON contacts.id_k_p = clients_leading.id_group  
JOIN kstatus on contacts.status = kstatus.id_kstatus 
ORDER BY contacts.date DESC;

But I'm wondering why is it so? It looks like in the firt statement joins are done first and then WHERE clause is applied and in second is just the opposite. But will it behave the same way on all DB engines (I tested it on MySQL)?

I was expecting DB engine can optimize queries like the fors one and firs apply WHERE clause and then make joins.

Upvotes: 1

Views: 687

Answers (2)

Jean-Christophe Duperron
Jean-Christophe Duperron

Reputation: 54

You always can replace join with nested query... It's always faster but lot messy...

Upvotes: 0

yasth
yasth

Reputation: 171

There are a lot of different reasons this could be (keying etc), but you can look at the explain mysql command to see how the statements are being executed. If you can run that and if it still is a mystery post it.

Upvotes: 2

Related Questions