mySql Query works locally but takes for ever on server-CodePudding

So I have a query that runs locally but is super slow on the server. I'm guessing its somthing to do with the alias but can't figure it out, its more than likely going to be somthing simple but I just can't see it. So heres the query.

The Query

SELECT DISTINCT
    `Acquisition_Source` as 'Source',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-12-01' AND '2022-12-31' AND Acquisition_Source = Source) as '2022-12',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-11-01' AND '2022-11-30' AND Acquisition_Source = Source) as '2022-11',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-10-01' AND '2022-10-31' AND Acquisition_Source = Source) as '2022-10',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-09-01' AND '2022-09-30' AND Acquisition_Source = Source) as '2022-09',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-08-01' AND '2022-08-31' AND Acquisition_Source = Source) as '2022-08',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-07-01' AND '2022-07-31' AND Acquisition_Source = Source) as '2022-07',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-06-01' AND '2022-06-30' AND Acquisition_Source = Source) as '2022-06',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-05-01' AND '2022-05-31' AND Acquisition_Source = Source) as '2022-05',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-04-01' AND '2022-04-30' AND Acquisition_Source = Source) as '2022-04',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-03-01' AND '2022-03-31' AND Acquisition_Source = Source) as '2022-03',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-02-01' AND '2022-02-28' AND Acquisition_Source = Source) as '2022-02',
    (SELECT COUNT(ID) FROM `customer_migration_details` WHERE Date_Opened BETWEEN '2022-01-01' AND '2022-01-31' AND Acquisition_Source = Source) as '2022-01'
FROM `customer_migration_details`
WHERE `Acquisition_Source` != ''

The Explain

PRIMARY customer_migration_details      ALL                 22635   90.00   Using where; Using temporary
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where
DEPENDENT SUBQUERY  customer_migration_details      ALL                 22635   1.11    Using where

The expected result

I've tried using COUNT(*) to no effect.

There is approx 20 sources and short of execuing this query for each source indivdually I'm stuck.

CodePudding user response：

Try refactoring the query to avoid running a subquery for each month.

This query uses a CASE expression in the SELECT clause to count the number of rows for each month, and the SUM function to add up the counts for each Acquisition_Source. The result is a pivot table that shows the counts for each month, grouped by Acquisition_Source.

SELECT `Acquisition_Source` as 'Source',
       SUM(CASE WHEN MONTH(Date_Opened) = 1 THEN 1 ELSE 0 END) as '2022-01',
       SUM(CASE WHEN MONTH(Date_Opened) = 2 THEN 1 ELSE 0 END) as '2022-02',
       SUM(CASE WHEN MONTH(Date_Opened) = 3 THEN 1 ELSE 0 END) as '2022-03',
       SUM(CASE WHEN MONTH(Date_Opened) = 4 THEN 1 ELSE 0 END) as '2022-04',
       SUM(CASE WHEN MONTH(Date_Opened) = 5 THEN 1 ELSE 0 END) as '2022-05',
       SUM(CASE WHEN MONTH(Date_Opened) = 6 THEN 1 ELSE 0 END) as '2022-06',
       SUM(CASE WHEN MONTH(Date_Opened) = 7 THEN 1 ELSE 0 END) as '2022-07',
       SUM(CASE WHEN MONTH(Date_Opened) = 8 THEN 1 ELSE 0 END) as '2022-08',
       SUM(CASE WHEN MONTH(Date_Opened) = 9 THEN 1 ELSE 0 END) as '2022-09',
       SUM(CASE WHEN MONTH(Date_Opened) = 10 THEN 1 ELSE 0 END) as '2022-10',
       SUM(CASE WHEN MONTH(Date_Opened) = 11 THEN 1 ELSE 0 END) as '2022-11',
       SUM(CASE WHEN MONTH(Date_Opened) = 12 THEN 1 ELSE 0 END) as '2022-12'
FROM `customer_migration_details`
WHERE `Acquisition_Source` != '' AND YEAR(Date_Opened) = 2022
GROUP BY `Acquisition_Source`;

Also make sure that you have an index on Acquisition_Source and Date_Opened columns in the customer_migration_details table. This will allow the query to scan the table more efficiently and improve performance.