MySQL: How to get around temporary table error on this query?

Question

ok I bugged my head on this the whole day today and can't get this to work, basically I'm trying to query randomly 12 photos from a temp table which contains all photos the active logged-in user not yet voted on.

The error is obviously #1137 - Can't reopen table: 'r1' which temp table can only be reference once.

    CREATE TEMPORARY TABLE unvoted_photos
    SELECT *
    FROM photo
    WHERE photo.photo_uid 
    NOT IN 
    ( SELECT photo_uid
    FROM vote
    WHERE vote.user_uid = '12345' ) ;

    SELECT photo_uid, caption, storage_path
      FROM unvoted_photos AS r1 
      JOIN (SELECT (RAND() * 
                    (SELECT MAX(id)
                    FROM unvoted_photos)) AS sid)
                    AS r2
     WHERE r1.id >= r2.id
     ORDER BY r1.id ASC
     LIMIT 12;

The random select query followed this example due to performance issue. Otherwise this certainly would work.

    SELECT ... FROM photo WHERE photo_uid NOT IN ( voted images subquery ) ORDER BY RAND() LIMIT 12

hm, may I suggest using `SELECT ... FROM photo WHERE photo_uid NOT IN ( voted images subquery ) ORDER BY RAND() LIMIT 12` ? should do the trick, even without the temp table — Najzero, Feb 07 '13 at 14:02
@Najzero well that certainly works but I kinda need the random select query part for the performance issue. You can follow this link and check out the 'Performance' comparison at the bottom: http://jan.kneschke.de/projects/mysql/order-by-rand/ — jerrytouille, Feb 07 '13 at 14:08
@jerrytouille hm yeah, thought about performance too but couldnt figure that creating the temp one is that much faster (i cant access the link you posted due companies firewall). Have a look here: http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function the accepted answer looks solid — Najzero, Feb 07 '13 at 14:19
@Najzero nice link, yet it's about the same as the 'example' hyperlink in my OP. My issue is the _unvoted_photos_ table reference which is a temp table created from the top part. Is there a way to combine the two parts so I don't need the temp _unvoted_photos_ table? — jerrytouille, Feb 07 '13 at 14:25

score 0 · Answer 1 · answered Feb 07 '13 at 14:58

In correspondence to the discussion on the question, here is a way of merging the two together that you should be able to run right away. I'd rewrite this with JOINs, but in this case I have dropped the temporary table into a subquery so you can see whats happened.

SELECT photo_uid, caption, storage_path
  FROM (
          SELECT photo_uid, caption, storage_path
          FROM photo
          WHERE photo.photo_uid 
          NOT IN 
          ( 
            SELECT photo_uid
            FROM vote
            WHERE vote.user_uid = '12345' 
          )

       ) r1 
  JOIN (SELECT (RAND() * 
                (SELECT MAX(id)
                FROM unvoted_photos)) AS sid)
                AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 12;

MySQL: How to get around temporary table error on this query?

1 Answers1