Code: Select all
SELECT COUNT(`click_views`.`id`) AS `clickviews`, `view_type`, `sites`.`id`, `site_name`
FROM `click_views`
INNER JOIN `sites` ON `click_views`.`site_id` = `sites`.`id`
WHERE `click_views`.`af_id` > 0
AND `click_views`.`view_type` != ''
GROUP BY `sites`.`id`, `view_type`Here is what EXPLAIN of the SQL shows
Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE sites ALL PRIMARY NULL NULL NULL 10 Using temporary; Using filesort
1 SIMPLE click_views ref site_id,view_type,af_id site_id 1 spiders.sites.id 316368 Using whereCode: Select all
CREATE TABLE `click_views` (
`id` int(11) NOT NULL auto_increment,
`ip` varchar(16) collate utf8_unicode_ci default NULL,
`af_id` int(11) NOT NULL default '0',
`campaign_id` int(11) NOT NULL default '0',
`url_id` int(11) default NULL,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`banner_id` mediumint(9) NOT NULL default '0',
`planID` mediumint(9) NOT NULL default '0',
`view_type` enum('v','c') collate utf8_unicode_ci NOT NULL default 'v',
`site_id` tinyint(4) NOT NULL default '0',
`redirect_id` int(11) NOT NULL default '0',
`sub` varchar(55) collate utf8_unicode_ci NOT NULL default 'N/A',
`galleryname` varchar(24) collate utf8_unicode_ci NOT NULL default '',
PRIMARY KEY (`id`),
KEY `redirect_id` (`redirect_id`),
KEY `ip` (`ip`),
KEY `site_id` (`site_id`),
KEY `campaign_id` (`campaign_id`),
KEY `view_type` (`view_type`),
KEY `date` (`date`),
KEY `af_id` (`af_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2214611 ;Code: Select all
CREATE TABLE `sites` (
`id` int(5) NOT NULL auto_increment,
`site_name` varchar(15) collate utf8_unicode_ci NOT NULL default '',
`commission` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`active` tinyint(1) default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;