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-l...@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 >