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?

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;