large query going very slow
Posted: Wed May 02, 2007 9:32 pm
In a nutshell I'm developing some statistics to track our users, based upon their clicks and impressions. Now, this table has gotten extremely large, and is growing very rapidly. It is approaching 30 million rows, and the query has become excessively slow
Now working on my local server with a reduced amount of data, approximatly 5 million rows, the query takes about 10 seconds. With the 30 million rows, it takes about 300 seconds.
Here is what EXPLAIN of the SQL shows
and here is my table structure of the click_views table
and heres the sites table
Now I am no database guru, although for the love of me cannot figure out a way to get this query to run fast. Am I being unrealistic about wanting to COUNT() 30 million rows fast?
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 ;