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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
