List:General Discussion« Previous MessageNext Message »
From:Simon Wilkinson Date:January 21 2011 3:42pm
Subject:Re: optimizing query
View as plain text  
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@stripped> 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
>

Thread
optimizing querySimon Wilkinson18 Jan
  • Re: optimizing queryMichael Dykman18 Jan
    • Re: optimizing queryMihail Manolov19 Jan
  • Re: optimizing querySteve Meyers19 Jan
    • Re: optimizing querySimon Wilkinson21 Jan