List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:October 9 2009 6:26pm
Subject:RE: Trying to index a table - cant figure out best way
View as plain text  
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@stripped]
>Sent: Friday, October 09, 2009 1:39 AM
>To: Daevid Vincent
>Cc: mysql@stripped
>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 <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