How to avoid using a temporary table for this simple query

How can I avoid using a temporary table for this query and still accomplishing the same goal?

EXPLAIN EXTENDED 
SELECT DISTINCT id, view_count
  FROM
      screenshot_udb_affect_assoc
  INNER JOIN
      screenshot ON id = screenshot_id
  WHERE unit_id = 110 LIMIT 0, 6

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  screenshot_udb_affect_asso开发者_Python百科c ref screenshot_id,unit_id   unit_id 4   const   34  100.00  Using temporary
1   SIMPLE  screenshot  eq_ref  PRIMARY PRIMARY 4   source_core.screenshot_udb_affect_assoc.screenshot...   1   100.00

I've updated the query to not use RAND() anymore and instead the LIMIT will be randomized via PHP. Though it still shows that it's using a temporary table

SELECT  rnd_id, rnd_value
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 6
        FROM    screenshot
        JOIN    screenshot_udb_affect_assoc
        ON      screenshot_id = id
        WHERE   unit_id = 110
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    (
                SELECT  id, view_count
                FROM    screenshot
                JOIN    screenshot_udb_affect_assoc
                ON      screenshot_id = id
                WHERE   unit_id = 110
                ) r 
        WHERE   (@cnt := @cnt - 1)
                AND RAND() < @lim / @cnt
        ) i

Explained in more detail in this article:

• Selecting random rows

This still needs two scans on all rows satisfying the query, but no filesort.

Create the following indexes:

screenshot (unit_id)
screenshot_udb_affect_assoc (screenshot_id)

ORDER BY RAND() automatically causes a temporary table to be created.

It works by creating a temporary table with a new column that is populated with random numbers. It then performs the query against the temporary table using ORDER BY.

To avoid using a temporary table, you will have to find a new method of selecting random numbers.

The solution I have used before (which relies on rows not being deleted) is to do a SELECT COUNT on the table, then picking random numbers in that range and only selecting those rows.

There are a number of other solutions, but ORDER BY RAND() is not a good solution.