Search code examples
mysqlcountsubqueryleft-joinquery-performance

Counting number of non-referenced rows in a table


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?


Solution

  • 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.