List:General Discussion« Previous MessageNext Message »
From:Steve Meyers Date:January 19 2011 7:11am
Subject:Re: optimizing query
View as plain text  
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