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 <span class='keyword_code'>LIMIT 1 OFFSET :offset;</span>
This solution relays on the nonstandard LIMIT clause (supported by MySQL, PostgreSQL, SQLite).
Last update: 221 days ago