Hi Daevid, Thanks - have tried this - but an explain still shows that its going through all the articles. See below - note these arent all records, ive taken a snapshot of the views for a 2 week period to test with:
This is without the index: 1 SIMPLE article_views ALL *NULL* *NULL* *NULL* *NULL* 78300 Using where; Using temporary; Using filesort 1 SIMPLE articles eq_ref PRIMARY PRIMARY 8 database.article_views.article_id 1 Using where Then with the date_idx(date); 1 SIMPLE article_views ALL date_idx *NULL* *NULL* *NULL* 78300 Using where; Using temporary; Using filesort 1 SIMPLE articles eq_ref PRIMARY PRIMARY 8 database.article_views.article_id 1 Using where No difference :/ And I used the query below so its only 1 week and not the 2-3 weeks in the table. Thanks anyway :) Ian 2009/10/9 Daevid Vincent <dae...@daevid.com> > ALTER TABLE articles ADD INDEX date_idx (date); > > > -----Original Message----- > > From: Ian [mailto:barnrac...@gmail.com] > > Sent: Thursday, October 08, 2009 12:23 PM > > To: mysql@lists.mysql.com > > 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 > > > >
