The query below takes about 9 seconds to execute even though I have index on the date fields. Use MySQL 4.1.
SELECT DATE_FORMAT( ADDTIME(trackclicks.day, '2:0:0'), '%Y-%m-%d' )
AS sts_date, trackclicks.raw_1 AS sts_raw, trackclicks.uni_1 AS sts_uni,
( SELECT COUNT( * )
FROM tracksales
WHERE sts_date = DATE_FORMAT( ADDTIME(
tracksales.date_utc, '2:0:0'
), '%Y-%m-%d' ) AND tracksales.site = '1'
) AS sts_sales
FROM trackclicks
WHERE trackclicks.day
BETWEEN '2007-09-01' AND '2007-09-30'
ORDER BY trackclicks.day ASC
Contains 1533 rows:
CREATE TABLE `trackclicks` (
`id` int(10) unsigned NOT NULL auto_increment,
`day` date NOT NULL default '0000-00-00',
`raw_1` int(10) unsigned NOT NULL default '0',
`uni_1` int(10) unsigned NOT NULL default '0',
`raw_2` int(10) unsigned NOT NULL default '0',
`uni_2` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `day` (`day`)
) ENGINE=InnoDB AUTO_INCREMENT=1534 DEFAULT CHARSET=latin1;
Contains 22726 rows:
CREATE TABLE `tracksales` (
`id` int(10) unsigned NOT NULL auto_increment,
`date_utc` datetime NOT NULL default '0000-00-00 00:00:00',
`site` tinyint(1) NOT NULL default '0',
`adv` varchar(20) NOT NULL default '',
`identify` varchar(50) NOT NULL default '',
`sidkey` varchar(50) NOT NULL default '',
`referurl` varchar(80) NOT NULL default '',
`subscription` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `date_utc` (`date_utc`)
) ENGINE=InnoDB AUTO_INCREMENT=22727 DEFAULT CHARSET=latin1;
