[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: optimizing query
From:       Simon Wilkinson <simon.wilkinson () gmail ! com>
Date:       2011-01-21 15:42:22
Message-ID: AANLkTi=JOR-UCrBTHmQuf-WJZ_V3OB+7SgUDH1W3U6Qq () mail ! gmail ! com
[Download RAW message or body]


Thanks for the suggestions everybody.

I added in columns to store the day, month and year of the created_at value,
and then added in an index on (newsletter_id, created_month, created_day),
and the the slow queries reduced from around 20 seconds to 0.5 seconds!  I
also removed the redundant indexes.

Cheers,

Simon

On 19 January 2011 02:11, Steve Meyers <steve-mysql-list@spamwiz.com> wrote:

> On 1/18/11 10:22 AM, Simon Wilkinson wrote:
>
>> SELECT articles.* FROM articles INNER JOIN newsletters ON
>> articles.newsletter_id = newsletters.id INNER JOIN users ON users.id =
>> newsletters.user_id WHERE users.id =12 AND MONTH(articles.created_at) =
>> '12'
>> AND DAY(articles.created_at) = '5' ORDER BY YEAR(articles.created_at),
>> LENGTH(articles.body);
>>
>
> Simon -
>
> There are a few issues that are slowing down your query.  First, you're
> running functions to calculate the month and day of each article that is
> looked at.  As an aside, are you sure you don't want the DAYOFMONTH()
> function?
>
> Second, it's ideal to have the where clause in your query filter down
> (using an index) to as few rows as possible of the first table.  Other
> tables you join should ideally be 1 to 1 from the first table.  To
> accomplish this, you would probably need the user_id in your articles table.
>
> Another aside -- I noticed you have index_articles_on_newsletter_id as well
> as index_articles_on_newsletter_id_and_created_at.  The first index is
> redundant, the second index will take care of it.  This will slow down your
> INSERT/UPDATE/DELETE queries to some degree.
>
> Steve
>


[prev in list] [next in list] [prev in thread] [next in thread]