mySql Query running super slow-CodePudding

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

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.

CodePudding user response：

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.