在连接表上替代 ORDER BY RAND()? [英] Alternative to ORDER BY RAND() on joined tables?

问题描述

我有一张大约 3500 件物品的表格.在另一个表中，我存储用户投票 - 项目 ID、用户 ID 和分数.当用户点击此脚本时，它必须随机选择一个他们尚未投票的项目.

I have a table of approx 3500 Items. In another table I'm storing user votes - the Item id, the user id and the score. When a user hits this script, it must randomly select an Item they haven't already voted on.

目前我正在使用 LEFT JOIN 然后测试 null 以找到他们尚未投票的项目集.问题是，在此派生表上使用 ORDER BY RAND() 会导致内存问题，因为派生集正被复制到临时表进行排序.

At the moment I'm using LEFT JOIN then testing for null to find the set of Items they haven't voted on. Trouble is, using ORDER BY RAND() on this derived table is causing memory issues as the derived set is being copied to a temporary table for sorting.

SELECT i.id 
FROM items i 
LEFT JOIN (
    SELECT id 
    FROM votes 
    WHERE voter_id=X
    ) v 
ON i.id=v.id 
WHERE v.id IS NULL 
ORDER BY RAND() 
LIMIT 1

无论如何，items 表并不大，但是有很多人同时点击这个脚本(他们中的许多人多次重新访问同一页面)MySQL 正在苦苦挣扎.

The items table isn't large by any means, but with many people hitting this script at the same time (and many of them revisiting the same page many times) MySQL is struggling.

常见的替代方法(选择低于 MAX() 的随机 id)将不起作用 - 还有其他方法吗?我是否更好地返回整个结果集 &用PHP随机挑一个?

The common alternatives (picking random id lower than the MAX()) won't work were - is there any other way? Am I better returning the whole resultset & using PHP to pick one at random?

推荐答案

Burçin Yazıcı - 我正在使用 MySQL，因此时间戳不会产生毫/微秒.我确实尝试使用 PHP 的 microtime() 并将其插入到查询中，但这仍然导致使用临时；解释时使用文件排序.

Burçin Yazıcı - I'm using MySQL so timestamps won't yield mili/micro seconds. I did try using PHP's microtime() and insert that into the query, but this still results in 'using temporary; using filesort' when explaining.

暂时我将查询未排序，返回所有结果，然后使用 PHP

For the time being I'm leaving the query unsorted, returning all results, then using PHP's

mysql_data_seek($result, rand(0, mysql_num_rows($result)-1));
$row=mysql_fetch_object($result);

选择随机行.我对 mysql_data_seek 的性能影响持谨慎态度，但它暂时减轻了数据库的压力.

to pick a random row. I'm cautious of the performance impact of mysql_data_seek but its taking the pressure off the database for now.

这篇关于在连接表上替代 ORDER BY RAND()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！