如何優化使用依賴子查詢的 MySQL 查詢

我有一個具有自定義圖像提要的網路應用程序。查詢的圖像基於使用者之前是否看過該圖像，並按 Hacker News 風格評分排序。

image 表中只有 1000 行，而 seen 表中大約有 40,000 行。

目前查詢在小型 EC2 LEMP 堆棧實例上需要 7 秒或更長時間。

SELECT 
   images.id AS image_id,
   images.created_at AS time_ago,
   pictures.name AS picture_name,
   pictures.location AS picture_location,
   images.like_count AS like_count,
   images.seen_count AS seen_count,
   images.like_count /
   POWER(
       (HOUR(TIMEDIFF(NOW(), images.created_at))+2), 0.5
   ) AS s
FROM
   images
LEFT JOIN 
   pictures ON images.picture_id = pictures.id
WHERE
   images.id NOT IN (SELECT 
       image_id
   FROM
       seen
   WHERE
       user_id = $user_id)
AND images.id NOT IN ('$exclude_images_string')
AND images.user_id != $user_id
AND images.like_count >= 10
AND images.moderated IS TRUE
ORDER BY s DESC
LIMIT 30

如何優化它以更快地執行？

**編輯：**這裡是圖像的創建表語句並看到：

   CREATE TABLE images (
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
 picture_id int(11) NOT NULL,
 user_id int(11) NOT NULL,
 moderated tinyint(1) DEFAULT NULL,
 seen_count int(11) NOT NULL DEFAULT '0',
 like_count int(11) NOT NULL DEFAULT '0',
 created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (id,picture_id,user_id),
 KEY USER (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=1246 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE seen (
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
 image_id int(11) NOT NULL,
 user_id int(11) NOT NULL,
 created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (id,image_id,user_id)
) ENGINE=InnoDB AUTO_INCREMENT=39107 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

您可以使用LEFT JOIN.

這樣，您就不需要為每個圖像查看子查詢。

LEFT JOIN
       seen
   ON
       seen.image_id = images.id
   AND
       seen.user_id = $user_id

然後只獲取尚未加入的行。

WHERE
  seen.id IS NULL

如果 Shanooooon 的建議還不夠，那麼

SELECT (stuff from x, plus picture stuff)
   FROM (
       SELECT ...
           (everything except `picture` stuff)
           LIMIT 30 ) x
   LEFT JOIN pictures ON x.picture_id = pictures.id

這很有幫助，因為它只會達到pictures30 次，而不是 1000 次。

引用自：https://dba.stackexchange.com/questions/111763