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 >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
