MySQL Forums
Forum List  »  Optimizer & Parser

Index optimizations for huge table
Posted by: Mennan Kara
Date: April 30, 2007 08:57PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Index optimizations for huge table
3023
April 30, 2007 08:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.