minte9
LearnRemember   ANTIPATTERNS





Context

App that displays advertisments You want to choose random ad on each view so that all advertisers have an even chance. A few weeks later, people are complaining that your website is too slow.

Antipattern

Sort data randomly 
 
SELECT * FROM bugs ORDER BY RAND() LIMIT 1;
Sorting by RAND means the sorting cannot benefit from an index.

Solution

Choose a random row using an offset 
 
SELECT ROUND(RAND() * (SELECT COUNT(*) FROM bugs); // offset

SELECT * FROM bugs LIMIT 1 OFFSET :offset;
This solution relays on the nonstandard LIMIT clause (supported by MySQL, PostgreSQL, SQLite).



Questions and answers:
Clink on Option to Answer




1. When using RAND() ...

  • a) you can't benefit from an index
  • b) you can't use offset

2. Using OFFSET instead of RAND ...

  • a) is fast
  • b) is slow


References: