List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 8 2009 10:13pm
Subject:RE: Trying to index a table - cant figure out best way
View as plain text  
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
> 

Thread
Trying to index a table - cant figure out best wayIan8 Oct
  • RE: Trying to index a table - cant figure out best wayDaevid Vincent9 Oct
    • Re: Trying to index a table - cant figure out best wayIan9 Oct
      • RE: Trying to index a table - cant figure out best wayJerry Schwartz9 Oct