mySql Query running super slow

8hhllhi2 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(130)

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.

mysql

来源：https://stackoverflow.com/questions/74874588/mysql-query-running-super-slow

1条答案

按热度按时间

4nkexdtk1#

Use a JOIN with GROUP BYor 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.

赞(0）回复(0）举报 2022-12-22

我来回答

mySql Query running super slow

1条答案

相关问题

热门标签

最新问答