Here is the code again using BBCode for clarity. I didn't see indicated anywhere that these forums accepted BBCode. I attempted to use [table] [/table] for the results of the explain. That did not work, so it seems it uses a limited subset. Is that correct?
SELECT
code,
count(*) as 'count'
FROM (
SELECT distinct p.promo_code, submitid,
IF(ISNULL(p.promo_code), IF(h.account_number = c2.account_number, 12,9),
IF(c.full_name = CONCAT(h.firstname,' ',h.lastname),
IF(FROM_UNIXTIME(p.start_date) > concat(CURDATE( ) , ' 23:59:59'),
1, /*Match Name, Future Start Date*/
IF(p.`status` = 'REDEEMED',
2, /*Match Name, Redeemed*/
IF(FROM_UNIXTIME(p.end_date) < concat(CURDATE( ) , ' 23:59:59'),
IF(ADDDATE(FROM_UNIXTIME(p.end_date), INTERVAL 3 DAY) >= concat(CURDATE( ) , ' 23:59:59'),
10, /*Match Name, Bufferd*/
5), /*Match Name, Expired*/
6) /*Match Name, Active*/
)
),
IF(FROM_UNIXTIME(p.start_date) > concat(CURDATE( ) , ' 23:59:59'),
3, /*Name Different, Future Start Date*/
IF(p.`status` = 'REDEEMED',
4, /*Name Different, Redeemed*/
IF(FROM_UNIXTIME(p.end_date) < concat(CURDATE( ) , ' 23:59:59'),
IF(ADDDATE(FROM_UNIXTIME(p.end_date), INTERVAL 3 DAY) >= concat(CURDATE( ) , ' 23:59:59'),
11, /*Name Different, Buffered*/
7), /*Name Different, Expired*/
8) /*Name Different, Active*/
)
)
)
) AS 'code'
FROM helpdesk h
LEFT JOIN promo_codes p
ON h.promo_code = p.promo_code
LEFT JOIN customer_promocode cp
ON p.promo_code = cp.promo_code
LEFT JOIN customers c
ON cp.customer_number = c.customer_number
LEFT JOIN customers c2
ON h.account_number = c2.account_number
LEFT OUTER JOIN redeem_queue r
ON p.promo_code = r.promo_code
WHERE
h.help_status = 0
AND h.in_view_by = ''
AND (p.start_date >= UNIX_TIMESTAMP(SUBDATE(CURDATE(), INTERVAL 1 YEAR))
OR p.start_date IS NULL)
AND (ISNULL(p.promo_code) OR ISNULL(r.promo_code))
) x
GROUP BY x.code
The result of EXPLAIN EXTENDED
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL \N \N \N \N 16419 100.00 Using temporary; Using filesort
2 DERIVED h ref help_status help_status 1 10555 100.00 Using where; Using temporary
2 DERIVED p eq_ref promo_code promo_code 18 twc_cluster.h.promo_code 1 100.00 Using where; Distinct
2 DERIVED cp eq_ref promo_code promo_code 18 twc_cluster.p.promo_code 1 100.00 Distinct
2 DERIVED c eq_ref customer_number customer_number 257 twc_cluster.cp.customer_number 1 100.00 Distinct
2 DERIVED c2 ref account_number account_number 18 twc_cluster.h.account_number 1 100.00 Using index; Distinct
2 DERIVED r ALL \N \N \N \N 1 100.00 Using where; Distinct
The tables:
CREATE TABLE `helpdesk` (
`submitid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`promo_code` VARCHAR(16) NOT NULL DEFAULT '',
`account_number` VARCHAR(16) NOT NULL DEFAULT '',
`firstname` VARCHAR(52) NOT NULL DEFAULT '',
`lastname` VARCHAR(52) NOT NULL DEFAULT '',
`phone` VARCHAR(15) NULL DEFAULT '',
`email` VARCHAR(100) NOT NULL DEFAULT '',
`message` TEXT NULL,
`subdomain` VARCHAR(64) NOT NULL DEFAULT '',
`ip_address` VARCHAR(18) NULL DEFAULT NULL,
`zip_code` CHAR(10) NULL DEFAULT NULL,
`help_status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`in_view_by` VARCHAR(100) NULL DEFAULT NULL,
`submit_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated` TIMESTAMP NULL DEFAULT NULL,
`last_updated_by` VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (`submitid`),
INDEX `account_number` (`account_number`),
INDEX `subdomain` (`subdomain`),
INDEX `help_status` (`help_status`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=58517
CREATE TABLE `promo_codes` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`promo_code` VARCHAR(16) NOT NULL,
`account_number` VARCHAR(16) NOT NULL,
`status` VARCHAR(8) NOT NULL,
`start_date` BIGINT(20) NOT NULL,
`end_date` BIGINT(20) NOT NULL,
`version_id` VARCHAR(50) NULL DEFAULT NULL,
`listdetail` VARCHAR(50) NULL DEFAULT NULL,
`cmcst_super_seg` VARCHAR(50) NULL DEFAULT NULL,
`import_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`times_redeemed` INT(11) NOT NULL DEFAULT '0',
`level_2_name` VARCHAR(255) NULL DEFAULT NULL,
`level_3_name` VARCHAR(255) NULL DEFAULT NULL,
`segment_description` VARCHAR(255) NULL DEFAULT NULL,
`level_2_name_line_of_business` VARCHAR(255) NULL DEFAULT NULL,
`df_cellname` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `promo_code` (`promo_code`),
INDEX `import_id` (`import_id`),
INDEX `account_number` (`account_number`),
INDEX `status` (`status`),
INDEX `level_3_name` (`level_3_name`),
INDEX `cmcst_super_seg` (`cmcst_super_seg`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=51716819
;
CREATE TABLE `customer_promocode` (
`custpromo_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`customer_number` VARCHAR(255) NULL DEFAULT '',
`promo_code` VARCHAR(16) NOT NULL DEFAULT '',
PRIMARY KEY (`custpromo_id`),
UNIQUE INDEX `promo_code` (`promo_code`),
INDEX `customer_number` (`customer_number`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=38198663
;
CREATE TABLE `customers` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`customer_number` VARCHAR(255) NOT NULL,
`account_number` VARCHAR(16) NOT NULL,
`full_name` VARCHAR(100) NOT NULL,
`delivery_address` VARCHAR(40) NOT NULL,
`city` VARCHAR(25) NOT NULL,
`state` CHAR(2) NOT NULL,
`zip` CHAR(10) NOT NULL,
`alternate_address` VARCHAR(40) NOT NULL,
`tenure_months` INT(11) NOT NULL DEFAULT '0',
`last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE INDEX `customer_number` (`customer_number`),
INDEX `account_number` (`account_number`),
INDEX `zip` (`zip`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=40913408
;
CREATE TABLE `redeem_queue` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`promo_code` VARCHAR(16) NOT NULL,
`ticket_id` INT(11) UNSIGNED NOT NULL,
`start_date` DATETIME NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`resolve_status` TINYINT(3) UNSIGNED NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
COMMENT='resolve_status:\r\n0: Active\r\n1: Completed\r\n2: Failed'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
This is a query I inherited. I do believe all of the nested IFs are required to break out the counts as needed.
I will definitely try using set @today = unix_timestamp(curdate(), ' 23:59:59') );
I am also working on archiving and possibly partitioning the largest table (promo_codes) which currently has 13,307,357 rows of data.