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
>
