2
\$\begingroup\$

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.

SQLFiddle

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?

\$\endgroup\$
2
  • \$\begingroup\$ I presume there is an index on (spooner_pec.year, spooner_pec.policy_number) or similar, right? \$\endgroup\$ – rolfl Aug 27 '15 at 20:36
  • \$\begingroup\$ @rolfl There is currently only an index on the id for it to be auto_incrementing I believe. Jamal, I apologize, I'll try to think of something more to the situation. \$\endgroup\$ – Brandon Shega Aug 27 '15 at 20:42
3
\$\begingroup\$

Currently your code scans the spooner_pec table 6 times, once each for the year-based columns, and again for the final result.

You can reduce this to just 2 times by caching the list of years in a temp table:

CREATE TEMPORARY TABLE GotYears (year varchar(10));

INSERT INTO GotYears
SELECT DISTINCT year
FROM spooner_pec

Now you have a small table with just a few years in it, instead of 3,000,000 records. You have scanned the table just once to do that too.

Now use that table to build your SQL select clauses....

SELECT GROUP_CONCAT(CONCAT(
     'max(case when year = ', year, ' then experience_rate end) AS `', year, '-Pen`' )
ORDER BY year ) INTO @sql1 FROM GotYears;

Having made that recommendation, though, I also recommend having an index on the year column which will make the scan a whole lot faster too.

The query is still really ugly ™ but that's a problem in your application and data storage.

Additionally, your final query is horrible, horrible, and horrible. A group-by query with many columns, but only grouping by the policy_id is very messy, and is MySQL specific. The values returned in the other columns are unspecified in terms of behaviour. I hope all your values in all the columns are the same or otherwise your output values are unspecified. It is a problem with your database structure too, that the data is not normalized. Why do you record the same values for the same policies in multiple records? Your database may be a data warehouse, but even then it is a bit of a mess.

You should audit your tables, and reconsider the design and normalization of your tables.

Still, if you apply the changes I suggest (add the year index, and have a single temp table for the year values), then I imagine your query will execute in about 5 seconds instead of 30.

\$\endgroup\$
1
  • \$\begingroup\$ Ya, all the data comes from a state, I don't really have control over how they have it, they just import the csv into the database and I have to change how it looks to export as a different csv. I appreciate your advice, I will try adding the temporary table and see how that helps. Thank you! \$\endgroup\$ – Brandon Shega Aug 27 '15 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.