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.
I execute this explain for every steps and every result it shows on the own PDF file.
I generate the explain on the current select (step_0.pdf)
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)
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.