SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Solution: In No Particular Order

Solution: In No Particular Order

Let's test some techniques for random selection in this lesson to solve the antipattern.

We'll cover the following...

The sort-by-random technique is an example of a query that’s bound to perform a table scan and an expensive manual sort. When we design solutions in SQL, we should be on the lookout for inefficient queries like this. Instead of searching fruitlessly for a way to optimize an unoptimizable query, we need to rethink our approach. We can use the alternative techniques shown in the following sections to query a random row from a query result set. In different circumstances, each of these solutions can produce the same result with greater efficiency than sort-by-random.

Choose a random key-value between 1 and MAX

One technique that avoids sorting the table is to choose a random value between 1 and the greatest primary key value.

MySQL
SELECT b1.*
  FROM Bugs AS b1
  JOIN (SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS rand_id) AS b2
    ON (b1.bug_id = b2.rand_id);

This solution assumes that:

  • the primary key values start at 1
  • the primary key values are contiguous, that is, there are no values unused between 1 and the greatest value. If there are gaps, a randomly chosen value may not match a row in the table.

We use ...