Hello there.
I have 2 table:
factors:
CREATE TABLE IF NOT EXISTS `factors` (
`vendor_id` varchar( COLLATE utf8_unicode_ci NOT NULL,
`factor_key` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
`factor_status` enum('0','1','2','3','4','5','6','7','8','9','10','11') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0: pending. 1: ready. 2:sent. 3:delivered. 4:canceled. 5:returned. 6:return_confirmed. 7: canceled_by_post. 8: banned. 9: waiting. 10: failed CASH. 11: transferred',
UNIQUE KEY `factor_key` (`factor_key`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
factor_times:
CREATE TABLE IF NOT EXISTS `factor_times` (
`factor_key` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
`factor_status` int(11) NOT NULL,
`factor_time` int(11) NOT NULL,
KEY `factor_key` (`factor_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Factor times will save timestamp for each status (0 to 11).
each factor_key can have unlimited statuses and times. for example, a factor can go to status 1, then go back to 0, then go to 2, then 3, then back to 1 ....
What i want, is to export first time of statuss of (0,2,3,6) for each day of the month.
for example, i want to export orders that been in status (0,2,3,6) in November, grouped day by day.
What i tried is something like this:
SELECT
`factor_times`.`factor_status`,
MIN(`factor_times`.`factor_time`)
FROM
`factor_times`
INNER JOIN
`factors`
ON
`factor_times`.`factor_key`=`factors`.`factor_key`
AND
`factors`.`vendor_id`='03010001'
WHERE
`factor_times`.`factor_status` IN (0,2,3,6)
AND
`factor_times`.`factor_time` > 1437507000
AND
`factor_times`.`factor_time` > 1440185400
GROUP BY
`factor_times`.`factor_key`
This used to export all times in a period, so i can use PHP code to assign them to each day of month.
But it takes so so so so so so long to execute.
factor rows: 246,718
factor_times rows: 2,915,127
Sorry for poor English :happy-04:
