Trying to index a table - cant figure out best way

--0016e6dab18e105d3b0475716449
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6dab18e105d3b0475716449--

RE: Trying to index a table - cant figure out best way

ALTER TABLE articles ADD INDEX date_idx (date);

> -----Original Message-----
> From: Ian [mailto:barnracoon@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=gcdmg-mysql-2@m.gmane.org

Re: Trying to index a table - cant figure out best way

--0016e6db2b0a4514ed04757a00b8
Content-Type: text/plain; charset=ISO-8859-1

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

> ALTER TABLE articles ADD INDEX date_idx (date);
>
> > -----Original Message-----
> > From: Ian [mailto:barnracoon@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
> >
>
>

--0016e6db2b0a4514ed04757a00b8--

RE: Trying to index a table - cant figure out best way

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:barnracoon@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
>
>> ALTER TABLE articles ADD INDEX date_idx (date);
>>
>> > -----Original Message-----
>> > From: Ian [mailto:barnracoon@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=gcdmg-mysql-2@m.gmane.org