CODEWITHSUNDEEP
mysql
user3310348
user3310348

Reputation:

mySql Query running super slow

I have seen a few post's regarding slow queries but none had the answer I'm hoping for.

I've been staring at this query for ages and for some reason cant see whats making this so damn slow dates such as 2022-01-01 > 2022-12-21 even taking 80 seconds....

So here is the query

SELECT
    accounts.first_name,
    accounts.last_name,
    accounts.email,
    (
    SELECT
        COUNT(ID)
    FROM
        customer_migration_details
    WHERE
        date_opened BETWEEN '2022-01-01' AND '2022-12-31' AND customer_migration_details.Assigned_to = accounts.email GROUP BY `accounts`.`email` LIMIT 1
) AS 'New Customers'
FROM
    customer_migration_details
RIGHT JOIN accounts ON customer_migration_details.Assigned_to = accounts.email
WHERE
    date_opened BETWEEN '2022-01-01' AND '2022-12-31' AND customer_migration_details.Assigned_to = accounts.email AND accounts.role LIKE '%Sales%'
GROUP BY
    `accounts`.`email`

Heres the results

enter image description here

but here is the annoying part.

Showing rows 0 - 7 (8 total, Query took 109.5797 seconds.)

Theres got to be something im missing in the subquery maybe thats causing this to take so long.

Upvotes: 2

Views: 55

Answers (2)

Rick James
Rick James

Reputation: 142208

acc:  INDEX(email)
cmd:  INDEX(Assigned_to, date_opened)

Having GROUP BY acc.email in the subquery seems wrong. And it may be unnecessary in the outer query.

Do not say COUNT(x) unless you need to avoid counting rows with x IS NULL. Instead, say simply COUNT(*).

If date_opened is a DATETIME, then you have excluded all but one second of New Years Eve.

LIKE with an initial wildcard is a performance problem. Are there multiple "roles" with "Sales" in them?

My brain gets scrambled when I see RIGHT JOIN. Can it be turned around to be a LEFT JOIN? Anyway, it seems to be an INNER JOIN.

Please provide EXPLAIN SELECT ...

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

Use a JOIN with GROUP BY or use a correlated sub-query, but not both at the same time.

SELECT
    accounts.first_name,
    accounts.last_name,
    accounts.email,
    COUNT(customer_migration_details.id)  AS new_customers
FROM
    accounts 
LEFT JOIN
    customer_migration_details
        ON  customer_migration_details.assigned_to = accounts.email
        AND customer_migration_details.date_opened BETWEEN '2022-01-01' AND '2022-12-31'
WHERE
    accounts.role LIKE '%Sales%'
GROUP BY
    accounts.email

Or...

SELECT
    accounts.first_name,
    accounts.last_name,
    accounts.email,
    (
      SELECT
          COUNT(ID)
      FROM
          customer_migration_details
      WHERE
              date_opened BETWEEN '2022-01-01' AND '2022-12-31'
          AND assigned_to = accounts.email
    )
      AS new_customers
FROM
    accounts
WHERE
    accounts.role LIKE '%Sales%'

Notes:

It's bad practice to put spaces, etc, in column names, so I changed New Customers to new_customers.

LIKE '%Sales%' can't use an index, so will scan each and every account row.

Upvotes: 0

Related Questions