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