mysql - 如何避免为这个简单的查询使用临时表

Question

我怎样才能避免为此查询使用临时表并仍然实现相同的目标？

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_assoc 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

我已将查询更新为不再使用 RAND()，而是 LIMIT 将通过 PHP 随机化。虽然它仍然显示它正在使用临时表

Answer 1

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

在这篇文章中有更详细的解释:

• Selecting random rows

这仍然需要对所有满足查询的行进行两次扫描，但不需要 filesort。

创建以下索引:

screenshot (unit_id)
screenshot_udb_affect_assoc (screenshot_id)