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.
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) =
>> AND DAY(articles.created_at) = '5' ORDER BY YEAR(articles.created_at),
> 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()
> 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.