From: Daevid Vincent
Date: October 8 2009 10:13pm
Subject: RE: Trying to index a table - cant figure out best way
List-Archive: http://lists.mysql.com/mysql/219008
Message-Id: <745B2D3456F5485E941CDEEB93EAF233@mascorp.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

ALTER TABLE articles ADD INDEX date_idx (date); 

> -----Original Message-----
> From: Ian [mailto:barnracoon@stripped] 
> Sent: Thursday, October 08, 2009 12:23 PM
> To: mysql@stripped
> Subject: Trying to index a table - cant figure out best way
> 
> Hi,
> 
> I have a table that stores article views per day per article:
> 
> `post_id` int(11) NOT NULL,
> `date` date NOT NULL,
> `views` int(11) NOT NULL
> 
> A couple of entries:
> 1987 2009-10-04 1744
> 1583 2009-10-04 2626
> 1238 2009-10-04 2211
> 
> This works fine - but I am now trying to figure out whats the 
> best way to
> add an index to this so that if I am running a query limiting 
> between dates
> I dont have to run through all the hundreds of thousands of 
> records to find
> a couple hundred results.
> 
> my query looks something like this (for 7 days top articles):
> SELECT articles.*, sum(views) AS views FROM articles LEFT 
> JOIN article_views
> ON article_views.article_id = articles.id WHERE ( date <= 
> '2009-10-07' AND
> date >= '2009-10-01') GROUP BY article_id
> 
> Any help would be greatly appreciated.
> 
> Thanks
> Ian
>