I have this query here that I am trying to make more efficient as it is taking an extremely long amount of time, about 40s on 3 million records.
The query basically takes X amount of rows per company and turns them into columns for a given existing column.
SET @sql = NULL;
SET @sql1 = NULL;
SET @sql2 = NULL;
SET @sql3 = NULL;
SET @sql4 = NULL;
SET @sql5 = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when year = ', year, ' then experience_rate end) AS `', year, '-Pen`' ) ORDER BY year ) INTO @sql1 FROM spooner_pec;
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when year = ', year, ' then mco_name end) AS `', year, '-MCO`' ) ORDER BY year ) INTO @sql2 FROM spooner_pec;
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when year = ', year, ' then premium_range end) AS `', year, '-Prem`' ) ORDER BY year ) INTO @sql3 FROM spooner_pec;
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when year = ', year, ' then employer_rating_plan end) AS `', year, '-Rating`' ) ORDER BY year ) INTO @sql4 FROM spooner_pec;
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'max(case when year = ', year, ' then risk_group_number end) AS `', year, '-Gr Num`' ) ORDER BY year ) INTO @sql5 FROM spooner_pec;
SET @sql = CONCAT(
'SELECT policy_number AS PolicyNumber,
coverage_status_code As CoverageStatusCode,
primary_name AS PrimaryName,
primary_dba_name AS DBA,
address1 AS Address1,
address2 AS Address2,
city AS CityName,
state AS StateID,
zipcode AS ZipCode,
zip_plus_four AS ZipCode4,
business_area_code AS PhoneAreaCode,
business_phone AS PhoneNumber,
business_extension AS PhoneExtension,
business_contact_first_name AS FirstName,
business_contact_last_name AS LastName,
county_description AS County, ', @sql1, ', ', @sql2, ',', @sql3, ',', @sql4, ',', @sql5, '
FROM spooner_pec GROUP BY policy_number');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
One of my coworkers mentioned indexing but I think the columns that are taking the longest are the ones that I am creating to pivot the data, is there any way to index those?
