如何通过RAND()函数优化MySQL的顺序？
如何通过RAND()函数优化MySQL的顺序？
提问于 2018-03-05 06:31:42
我想优化我的查询，大多数缓慢查询都包含ORDER BY RAND()。
这个问题有什么解决办法吗？
一个例子：
SELECT accomodation.ac_id,
accomodation.ac_status,
accomodation.ac_name,
accomodation.ac_status,
accomodation.ac_images
FROM accomodation, accomodation_category
WHERE accomodation.ac_status != 'draft'
AND accomodation.ac_category = accomodation_category.acat_id
AND accomodation_category.acat_slug != 'vendeglatohely'
AND ac_images != 'b:0;'
ORDER BY
RAND()
LIMIT 1回答 2
Stack Overflow用户
发布于 2018-03-05 15:06:22
试试这个：
SELECT *
FROM (
SELECT @cnt := COUNT(*) + 1,
@lim := 10
FROM t_random
) vars
STRAIGHT_JOIN
(
SELECT r.*,
@lim := @lim - 1
FROM t_random r
WHERE (@cnt := @cnt - 1)
AND RAND(20090301) < @lim / @cnt
) i这里的主要思想是，不排序，而是保留两个变量并计算running probability当前步骤中要选择的行的。
如果您只需要选择一个随机记录，请尝试如下：
SELECT aco.*
FROM (
SELECT minid + FLOOR((maxid - minid) * RAND()) AS randid
FROM (
SELECT MAX(ac_id) AS maxid, MIN(ac_id) AS minid
FROM accomodation
) q
) q2
JOIN accomodation aco
ON aco.ac_id =
COALESCE
(
(
SELECT accomodation.ac_id
FROM accomodation
WHERE ac_id > randid
AND ac_status != 'draft'
AND ac_images != 'b:0;'
AND NOT EXISTS
(
SELECT NULL
FROM accomodation_category
WHERE acat_id = ac_category
AND acat_slug = 'vendeglatohely'
)
ORDER BY
ac_id
LIMIT 1
),
(
SELECT accomodation.ac_id
FROM accomodation
WHERE ac_status != 'draft'
AND ac_images != 'b:0;'
AND NOT EXISTS
(
SELECT NULL
FROM accomodation_category
WHERE acat_id = ac_category
AND acat_slug = 'vendeglatohely'
)
ORDER BY
ac_id
LIMIT 1
)
)这假设你的ac_id它们的分布差不多是均匀的。
Stack Overflow用户
发布于 2018-03-05 15:54:59
可以：
SELECT [fields] FROM [table] WHERE id >= FLOOR(RAND()*MAX(id)) LIMIT 1或者：
- 添加一个名为
random然后用随机数填满它。然后，您可以在PHP中生成一个随机数，然后执行以下操作"SELECT ... WHERE rnd > $random" - 获取整个ID列表并将其缓存在文本文件中。读取文件并从中选择一个随机ID。
- 将查询结果缓存为HTML，并将其保存几个小时。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100003575
复制相关文章
相似问题