I have a table addresses
and many tables that has a address_id
column that references an address (all have foreign key contraints). I want to find the number of addresses that are not referenced from any of the other tables.
I've tried the following query, but it is VERY slow:
SELECT COUNT(*)
FROM addresses A
LEFT JOIN customers C ON C.address_id = A.id
LEFT JOIN agreements AG ON AG.address_id = A.id
LEFT JOIN products P ON P.address_id = A.id
LEFT JOIN letters L ON L.address_id = A.id
WHERE C.id IS NULL
AND AG.id IS NULL
AND P.id IS NULL
AND L.id IS NULL
Is there any way I can do this, without the query taking forever?
I would start by rewriting this with not exists
:
select count(*)
from addresses a
where
not exists (select 1 from customers c where c.address_id = a.id)
and not exists (select 1 from agreements g where g.address_id = a.id)
and not exists (select 1 from products p where p.address_id = a.id)
and not exists (select 1 from letters l where l.address_id = a.id)
Then, make sure to have the following indexes in place to speed up the query:
customers(address_id)
agreements(address_id)
products(address_id)
letters(address_id)
If you have properly defined the address_id
columns as foreign keys to address(id)
, then these indexes are already there.