paolo@bimodesign.com | +34 608 61 64 10

E-Commerce

        

Couponic, improve a complex select performance

I created this a only select statment to generate a complex report to list all the sales in a Couponic application, in order to send a csv file to the administrator (via email).
The time taken to generate the 12 thousand cvs rows is around the 5 minutes, so I need to improve it and the first step it has been creating indexes. The next will be create partition (cause the report affects 8 mounths) and finally I'm going to create view or temporary table to process these data.
But in this post I'll show only the steps to create the indexs.

Note
I execute this explain for every steps and every result it shows on the own PDF file.

Step 0
I generate the explain on the current select (step_0.pdf)

Step 1
The cpns_JgredeemCodes table has only the primary indexes and generate a FULL SCAN. So I create an index on the field oldRedemptionCode of the where condition

ALTER TABLE `cpns_JgredeemCodes` ADD INDEX `idx_jgredeemCodes_oldredemptionCode` (`oldRedemptionCode`);

The goal is that I haven't Full Scan(step_1.pdf)

Step 2
The cpns_I18N table has only the primary indexes, so I create these two indexs on the fields

ALTER TABLE `cpns_I18N` ADD INDEX `idx_I18N_name` (`name`);
ALTER TABLE `cpns_I18N` ADD INDEX `idx_I18N_model` (`model`);

But in this case it doesn't produce the result that I hope.
So, I drop these two indexes

DROP INDEX idx_I18N_name ON cpns_I18N;
DROP INDEX idx_I18N_model ON cpns_I18N;

Instead of these two indexs, I create the index for the field relatedId, that is JOIN

ALTER TABLE `cpns_I18N` ADD INDEX `idx_I18N_relatedid` (`relatedId`);

The goal is that I haven't Full Scan(step_2.pdf)

With these simple indexs, I improve the time executing the select, from 8 minutes to 45 seconds.