CODEWITHSUNDEEP
mysqlsqlperformanceoptimizationquery-optimization
Trent
Trent

Reputation: 5877

Any ideas on how could I optimize this query?

This query is a bit slow, and I'd like to optimize it, any ideas?

SELECT DISTINCT a.id
FROM   article a
       LEFT JOIN article_comment ac
         ON a.id = ac.article_id
       LEFT JOIN comment c
         ON ac.id = c.id
WHERE  a.id IN (SELECT a2.id
                  FROM   article_user_read aur
                         LEFT JOIN article a2
                           ON aur.article_id = a2.id
                  WHERE  c.published_date > aur.read_date
                     AND aur.user_id = 36748
                     AND aur.followed = 1)
ORDER  BY c.published_date DESC

here is the article_user_read table :

CREATE TABLE `article_user_read` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `read_date` datetime NOT NULL,
  `followed` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_BBE52A0262922701A76ED395` (`article_id`,`user_id`),
  KEY `IDX_BBE52A0262922701` (`article_id`),
  KEY `IDX_BBE52A02A76ED395` (`user_id`),
  CONSTRAINT `article_user_read_ibfk_3` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`),
  CONSTRAINT `article_user_read_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20193 DEFAULT CHARSET=latin1;

The other are just simple article & user table.

Upvotes: 0

Views: 94

Answers (4)

DRapp
DRapp

Reputation: 48139

Since you are only concerned with distinct IDs, I would pre-query just from the article_user_read table since that is the whole basis of your WHERE EXISTS, then link to articles and pull whatever other columns you want... Additionally, you were doing LEFT JOINs, but ultimately requiring a find based on your respective WHERE clause of the "c.published_date" which means all entries are required all the way down to the comments table. So LEFT JOIN is not applicable... the only other thing I would do is add an index to the article_user_read table on BOTH the User ID AND the Followed flag.

SELECT distinct 
      aur.article_id
   from 
      article_user_read aur
         join article a
            on aur.article_id = a.id
         join article_comment ac
            on aur.article_id = ac.article_id
            join comment c
               on ac.id = c.id
              and c.published_date > aur.read_date
   where
          aur.user_id = 36748
      and aur.followed = 1

Upvotes: 0

gonsalu
gonsalu

Reputation: 3184

SELECT DISTINCT a.id
  FROM article AS a

  JOIN article_user_read AS aur
    ON aur.article_id = a.id

  JOIN comment AS c
    ON ac.id = c.id

  JOIN article_comment AS ac
    ON a.id = ac.article_id

 WHERE c.published_date > aur.read_date
   AND aur.user_id = 36748
   AND aur.followed = 1

 ORDER BY c.published_date DESC

It doesn't make sense to LEFT JOIN article_user_read with article, because you're interested in articles, so you don't care if there are articles without a article_user_read relation -- that can be optimized to a JOIN.

You also only want articles which have a comment after the user reads the article, so the relation between article, article_comment and comment must exist -- that can be optimized to JOINs.

The main change however, is that you don't need a correlated sub-query (referencing comment c in the sub-query), so the JOIN between article_user_read and article can be pushed to the main query.

Upvotes: 1

user359040
user359040

Reputation:

Given that you are only interested in the article IDs in this query, it can be significantly simplified to:

SELECT a.id
FROM article a
WHERE EXISTS
(SELECT NULL
 FROM article_comment ac
 INNER JOIN comment c ON ac.id = c.id
 INNER JOIN article_user_read aur 
         ON aur.article_id = a.id AND c.published_date > aur.read_date
 WHERE a.id = ac.article_id AND
       aur.user_id = 36748 AND
       aur.followed = 1)

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

A wild guess: replacing "IN" by "WHERE EXISTS" often pays off:

SELECT DISTINCT a.id
FROM   article a
LEFT JOIN article_comment ac
     ON a.id = ac.article_id
LEFT JOIN comment c
     ON ac.id = c.id
WHERE EXISTS ( 
    SELECT *
    FROM article_user_read aur
    LEFT JOIN article a2
      ON aur.article_id = a2.id
    WHERE  c.published_date > aur.read_date
      AND aur.user_id = 36748
      AND aur.followed = 1
      AND a.id = a2.id
      )
ORDER BY c.published_date DESC

Upvotes: 0

Related Questions