1

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; 
  • The multi-column primary keys are not doing anything useful for you, since they include an auto-increment. You don't seem to need the "id" in "seen" at all, unless a user could have "seen" an image more than once, which this design does not prevent. – Michael - sqlbot Aug 21 '15 at 3:21
  • What is the dependent subquery that your title is mentioning? I can't see any. The subquery that you do have is not a dependent one, unless I'm misunderstanding what you mean by "dependent". – Andriy M Aug 21 '15 at 5:41
  • @AndriyM there is a bug/behaviour in mysql optimmizer where it does not materialize the subquery but evaluates it once per main row even when there is no "parameter dependency" or how should I call that. That one shows in explain as "dependent" too. – jkavalik Aug 21 '15 at 5:44
  • @GusRuss89 could you add EXPLAIN for that query too? Usual solution for this is the join, but you could try to use one simple workaround - wrap the subquery in another simple subquery select image_id from (<your subquery>) - that makes optimizer materialize the innermost query and only evaluate the simple one as dependent - compare sqlfiddle.com/#!9/0a3d2/2 and sqlfiddle.com/#!9/0a3d2/3 (check the "View Execution Plan" button) to see the results. – jkavalik Aug 21 '15 at 5:50
  • @jkavalik I've heard more than once that in MySQL an anti-join works faster if it's implemented as LEFT JOIN + WHERE IS NULL, rather than as NOT IN or NOT EXISTS, but never realised what was the reason behind it. Your explanation seems to finally clarify that for me (in addition to answering my "dependent" question), thanks. – Andriy M Aug 21 '15 at 5:50
2

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
  • Also, seen needs INDEX(user_id, image_id) – Rick James Aug 22 '15 at 6:31
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.