mardi 14 juin 2016

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?

Aucun commentaire:

Enregistrer un commentaire