Optimizing MySQL query removing subquery

Question

Having these tables:

customers
---------------------
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` varchar(100) collate utf8_unicode_ci default NOT NULL,
....

customers_subaccounts
-------------------------
`companies_id` mediumint(8) unsigned NOT NULL,
`customers_id` mediumint(8) unsigned NOT NULL,
`subaccount` int(10) unsigned NOT NULL

I need to get all the customers whom have been assigned more than one subaccount for the same company.

This is what I've got:

SELECT * FROM customers 
WHERE id IN 
    (SELECT customers_id 
     FROM customers_subaccounts
     GROUP BY customers_id, companies_id 
     HAVING COUNT(subaccount) > 1)

This query is too slow though. It's even slower if I add the DISTINCT modifier to customers_id in the SELECT of the subquery, which in the end retrieves the same customers list for the whole query. Maybe there's a better way without subquerying, anything faster will help, and I'm not sure whether it will retrieve an accurate correct list.

Any help?

Answer 1

You can replace the subquery with an INNER JOIN:

SELECT t1.id
FROM customers t1
INNER JOIN
(
    SELECT DISTINCT customers_id 
    FROM customers_subaccounts
    GROUP BY customers_id, companies_id 
    HAVING COUNT(*) > 1
) t2
    ON t1.id = t2.customers_id

Answer 2

You can also try using EXISTS() which may be faster then a join :

SELECT * FROM customers t
WHERE EXISTS(SELECT 1 FROM customers_subaccounts s
             WHERE s.customers_id = t.id
             GROUP BY s.customers_id, s.companies_id 
             HAVING COUNT(subaccount) > 1)

You should also considering adding the following indexes(if not exists yet) :

customers_subaccounts (customers_id,companies_id,subaccount)
customers (id)