CODEWITHSUNDEEP
sqlsqliteandroid-sqlite
Timo Theobald
Timo Theobald

Reputation: 25

What to add to SELECT to output 10 RANDOM entries QUICKLY?

How to make 

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%'

output 10 random entries quickly?

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If you want quick, then order by random() isn't going to be fast. Perhaps this will speed things up:

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;

This limits the final order by to no more than 1000 rows and prevents an order by on a full cartesian product.

Upvotes: 1

Related Questions