user3514092 user3514092 - 5 months ago 11
MySQL Question

Optimizing MySQL query removing subquery

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

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