sql - 什么要添加到SELECT以快速输出10个RANDOM条目？

Question

怎么做

SELECT w1.wort AS column1,
       w2.wort AS column2,
       w3.wort AS column3
FROM woerter AS w1,
     woerter AS w2,
     woerter AS w3
WHERE w1.wort LIKE 'a%'
  AND w2.wort LIKE 'm%'
  AND w3.wort LIKE 'o%'

快速输出10个随机条目？

Answer 1

如果要快速，则order by random()不会很快。也许这会加快速度：

SELECT w1.wort AS column1,
       w2.wort AS column2,
       w3.wort AS column3
FROM (select w1.* from woerter w1 where w1.wort LIKE 'a%' order by random() limit 10) w1 cross join
     (select w2.* from woerter w2 where w2.wort LIKE 'm%' order by random() limit 10) w2 cross join
     (select w3.* from woerter w3 where w3.wort LIKE 'o%' order by random() limit 10) w3
ORDER BY random()
LIMIT 10;

这将最终的 order by限制为不超过1000行，并防止在完整的笛卡尔积上出现 order by。