This issue pre-dates databases. It was chewed over during the days of simple ISAM files. The big trade-off is between speed of retrieval and speed of insertion. I don't recall that you gave us any specifics about that. If you can afford the INSERT overhead, by all means index the date.
I see that you're doing a LEFT JOIN article_views ON article_views.article_id = articles.id. Is there an index on article_view.artical_id? Indexed or not, I wonder if using BETWEEN instead of two comparisons would be faster. By the way, when you post the output of an EXPLAIN try using "\G" instead of ";". I think it's easier to read. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com >-----Original Message----- >From: Ian [mailto:barnrac...@gmail.com] >Sent: Friday, October 09, 2009 1:39 AM >To: Daevid Vincent >Cc: mysql@lists.mysql.com >Subject: Re: Trying to index a table - cant figure out best way > >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 >> > >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
