How to speed up a SELECT query with nested queries?

Question

MySQL

Tell me how you can simplify such a request .. 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

Answer 1

indexes + foreign keys

Answer 2

This:

AND addresses.id_address NOT IN (
        SELECT id_address FROM clients_addresses WHERE id_address = addresses.id_address
    )

replace with:
If the addresses 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 ....