Optimizing the output of MySQL queries into a subquery

Question

The presence of 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 customers who have been assigned more than one subaccount for the same company.

This is what I have:

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

This request is too slow, though. This is even slower if I add the DISTINCT modifier to customer_id in the SELECT of the subquery, which at the end gets the same list of clients for the entire query. Perhaps there is a better way without a subquery, something will help faster, and I'm not sure if it will find the correct exact list.

Any help?

Answer 1

You can replace the subquery with 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

EXISTS(), , :

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)

( ):

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

Answer 3

, (, , ), :

select c.*
from (select distinct cs.customers_id
      from customers_subaccounts cs join
           customers_subaccounts cs2
           on cs.customers_id = cs2.customers_id and
              cs.companies_id = cs2.companies_id and
              cs.subaccount < cs2.subaccount
     ) cc join
     customers c
     on c.customers_id = cc.customers_id;

, customers_subaccounts(customers_id, companies_id, subaccount).

. , subaccounts , . , customers_subaccounts.

Answer 4

;)

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

index customers_id.

Answer 5

, - . mysql , :

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

I used it many years ago and it helped me a lot.