CODEWITHSUNDEEP
mysqlsql
Refael Sap
Refael Sap

Reputation: 11

WHERE HAVING COUNT(*) Very slow query

I'm trying to run a SQL query which takes 32 sec to complete, any ideas how to run it faster?

SELECT accounts.*
FROM accounts
WHERE accounts.account_id IN (SELECT map.account_id 
                              FROM  map 
                              WHERE map.account_id=accounts.account_id                              
                              HAVING COUNT(*)<2) 
ORDER BY rand() 
LIMIT 1

Upvotes: 0

Views: 165

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

You could use an inner join to achieve that filtering:

SELECT accounts.*
FROM accounts
INNER JOIN (
    SELECT map.account_id
    FROM map
    GROUP BY map.account_id
    HAVING COUNT(*) < 2
    ) AS m ON accounts.account_id = m.account_id
ORDER BY rand() 
limit 1

I'm not sure why you are ordering by rand or limiting the result to 1 but could it be repetitive execution of this overall query is a performance issue?

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try converting In clause to EXISTS clause -

SELECT accounts.*
  FROM accounts
 WHERE EXISTS (SELECT NULL
                 FROM map 
                WHERE map.account_id=accounts.account_id 
                GROUP BY map.account_id
               HAVING COUNT(*) < 2
               ) 
ORDER BY rand() 
limit 1

Upvotes: 0

Related Questions