List:General Discussion« Previous MessageNext Message »
From:Ian Date:October 9 2009 5:39am
Subject:Re: Trying to index a table - cant figure out best way
View as plain text  
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 <daevid@stripped>

> 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