php - 在大型 mysql 数据库上按 rand() 排序

Question

由于这个 mysql 查询，我的网站速度非常慢。

SELECT content.*,
       (SELECT content_views.views FROM content_views WHERE content_views.content = content.record_num) as views ,
       (SELECT images.filename FROM images WHERE images.record_num = content.thumbnail) AS thumbfile
FROM content WHERE enabled = 1  $filterAnd
ORDER BY RAND()
LIMIT $from,$max_results

我知道最好的方法是像这样使用它:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

但是我正在使用多个 mysql 表，我不知道如何使用我的查询，有人可以帮助我吗？

谢谢

编辑:

我已经尝试过:

SELECT content.*,
       (SELECT content_views.views FROM content_views WHERE content_views.content = content.record_num) as views ,
       (SELECT images.filename FROM images WHERE images.record_num = content.thumbnail) AS thumbfile
FROM content AS r1 JOIN
     (SELECT (RAND() * (SELECT MAX(record_num) FROM content)) AS id) AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1

但现在我收到错误:未知表“内容”

Answer 1

此代码从内容表中随机选择一行。

(SELECT 
    *
FROM
    content AS r1
        JOIN
    (SELECT 
        CEIL(RAND() * (SELECT 
                    MAX(id)
                FROM
                    content WHERE enabled = 1 $filterAnd)) AS id
    ) AS r2
WHERE
    r1.id >= r2.id AND r1.enabled=1 AND $filterAnd
ORDER BY r1.id ASC
LIMIT 1) randomTable

现在您需要将此查询视为一个表，其名称为 randomTable 字段，如 content 表中所示。现在您可以从中选择、加入它，完成您需要的所有操作。注意 $filterAnd - 我使用了两次。仅当 $filterAnd 包含与 content 表相关的条件时，此功能才有效。