Index optimizations for huge table

[Mennan Kara]

Hello,
I have a table containing about a hunderd million rows and it's growing 5-10 millions per day. The table structure is:

CREATE TABLE  `interad2`.`phpads_adviews` (
  `bannerid` mediumint(9) NOT NULL default '0',
  `zoneid` mediumint(9) NOT NULL default '0',
  `t_stamp` datetime default NULL,
  `host` varchar(255) collate latin1_general_ci NOT NULL,
  `source` varchar(50) collate latin1_general_ci NOT NULL,
  `country` char(2) character set utf8 NOT NULL,
  `U1` char(40) collate latin1_general_ci NOT NULL,
  `U2` char(40) collate latin1_general_ci NOT NULL,
  `clientid` mediumint(9) NOT NULL default '0',
  `affiliateid` mediumint(9) NOT NULL default '0',
  KEY `date` (`t_stamp`),
  KEY `banneriddate` (`bannerid`,`t_stamp`),
  KEY `zoneid` (`zoneid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC;

This table stores adview logs for our campaigns. U1 is an unique id for each adviewer.

I need to generate daily, weekly and campaign total reports from this table. The queries I'm using:

Campaign Reach Query

select clientid, count(*), count(distinct U1) from phpads_adviews group by clientid

EXPLAIN output:

id, select_type, table           , type , possible_keys, key, key_len, ref, rows     , Extra
 1, 'SIMPLE'   , 'phpads_adviews', 'ALL', ''           , '' , ''     , '' , 107818901, 'Using filesort'

Full Report

SELECT clientid,bannerid,affiliateid,zoneid,COUNT(*),COUNT(DISTINCT U1)
FROM phpads_adviews
GROUP BY bannerid,zoneid

EXPLAIN output:

id, select_type, table           , type , possible_keys, key, key_len, ref, rows     , Extra
 1, 'SIMPLE'   , 'phpads_adviews', 'ALL', ''           , '' , ''     , '' , 107818901, 'Using filesort'

Daily Report for a Date Period

SELECT clientid,bannerid,affiliateid,zoneid,date(t_stamp),COUNT(*),COUNT(DISTINCT U1)
FROM phpads_adviews
WHERE t_stamp>='20070423000000' AND t_stamp<'20070430000000'
GROUP BY bannerid,zoneid,date(t_stamp)

EXPLAIN output:

id, select_type, table           , type , possible_keys, key, key_len, ref, rows     , Extra
 1, 'SIMPLE'   , 'phpads_adviews', 'ALL', 'date'        , '', ''     , '' , 107818901, 'Using where; Using filesort'

What kind of indexes could help me to optimize these queries?

Thanks,
Mennan Kara



Edited 3 time(s). Last edit at 04/30/2007 09:50PM by Mennan Kara.