Search code examples
sqlpostgresqlselectquery-optimizationdistinct

SELECT DISTINCT query taking too long SQL

This is my code below, it's taking a very a long time to execute. When I add the SELECT DISTINCT it makes it very long.

What I'm trying to do is get unique companies that satisfy these conditions and also calculate how many teams each company has (this is given by team_id which is given to each user in auth_user u table).

Any help would be amazing, I want to learn how to make better SQL queries. I know that GROUP BY is the better way to do this, but I can't seem to get it.

SELECT DISTINCT u.company_id, c.name, c.company_type, c.office_location, (SELECT (COUNT(DISTINCT u.team_id)) FROM auth_user u WHERE u.company_id = c.id GROUP BY u.company_id) as number_of_teams, s.status, h.auto_renewal
FROM auth_user u, companies_company c, subscriptions_subscription s, hubspot_company h
    WHERE u.company_id = c.id
        AND s.company_id = c.id
        AND h.myagi_id = c.id
ORDER BY u.company_id ASC
Solution

  • First of all refactor your query to use the 1992 JOIN syntax instead of your grandpa's comma-join syntax. (I'm a grandpa and I jumped at using JOIN as soon as it became available.)

    SELECT DISTINCT u.company_id, c.name, c.company_type, c.office_location, 
       count_of_teams_TODO,
       s.status, h.auto_renewal
  FROM auth_user u
  JOIN companies_company c ON u.company_id = c.id
  JOIN subscriptions_subscription s ON  s.company_id = c.id
  JOIN hubspot_company h ON h.myagi_id = c.id
 ORDER BY u.company_id ASC;

    Then, I believe each user belongs to one team; that is has one value of auth_user.team_id. And you want your result set to show how many teams the company has.

    So substitute COUNT(DISTINCT u.team_id) teams for my count_of_teams_TODO placeholder, getting this. There's no need for a subquery. But for the aggregate function COUNT() we need GROUP BY. And we want to group by company, status, and autorenewal.

    SELECT c.id, company_id, c.name, c.company_type, c.office_location, 
       COUNT(DISTINCT u.team_id) teams,
       s.status, h.auto_renewal
  FROM auth_user u
  JOIN companies_company c ON u.company_id = c.id
  JOIN subscriptions_subscription s ON  s.company_id = c.id
  JOIN hubspot_company h ON h.myagi_id = c.id
 GROUP BY c.id, s.status, h.auto_renewal
 ORDER BY u.company_id ASC;

    And that should do it. Study up on GROUP BY and aggregate functions. Every second you spend learning those concepts better will help you.

    As far as performance goes, get this working and then ask another question. Tag it with and read this before you ask it.