How to speed up SELECT query with nested queries?

0 like 0 dislike
8 views
Tell me how can I simplify this query to speed up..
SELECT addresses.*, accounts.id_account, accounts.name as name_account, clients_addresses.id_client as on_client, clients.name FROM addresses LEFT JOIN accounts ON accounts.id_account = addresses.id_account LEFT JOIN clients_addresses ON clients_addresses.id_address = addresses.id_address LEFT JOIN clients ON clients_addresses.id_client = clients.id_client WHERE addresses.id_project = 10 AND addresses.id_address NOT IN ( SELECT id_address FROM clients_addresses WHERE id_address = addresses.id_address ) AND addresses.id_account = 65 GROUP BY addresses.id_address ORDER BY address desc LIMIT 0,100
by | 8 views

2 Answers

0 like 0 dislike
indexes + foreign keys
by
0 like 0 dislike
Here it is:
AND addresses.id_address NOT IN ( SELECT id_address FROM clients_addresses WHERE id_address = addresses.id_address )

replace with:
AND clients_addresses.id_address IS NULL

Addresses if the table is large,
you can do:
SELECT a.*, accounts.id_account, accounts.name as name_account, clients_addresses.id_client as on_client, clients.name FROM (SELECT * FROM addresses WHERE id_project = 10 AND id_account = 65 ) a LEFT JOIN ....
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked May 20, 2019 by Dvoeglazov
0 like 0 dislike
2 answers
0 like 0 dislike
3 answers
asked Jun 3, 2019 by xonar
110,608 questions
257,186 answers
0 comments
32,758 users