How to optimise MySQL query that uses dependent subquery

Question

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score.

There are only 1000 rows in the image table and about 40,000 rows in the seen table.

The current query is taking 7 seconds or more on a small EC2 LEMP stack instance.

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

How could this be optimised to run faster?

Edit: Here are the create table statements for images and seen:

    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; 

Answer 1

Instead of using a sub query you could use a LEFT JOIN.

That way you negate the need for a sub query which is being looked at for each image.

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

Then only get rows that haven't been joined.

WHERE
   seen.id IS NULL

Answer 2

If Shanooooon's suggestion is not enough, then

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

This helps because it will reach into pictures only 30 times, not 1000 times.