How to optimize ORDER BY RANDOM()

Doing a ORDER BY RAND() in SQL is bad. Very bad. As Tobias Petry details (and Bernard Grymonpon always used to tell at local meetups):

Ordering records in a random order involves these operations:

  1. Load all rows into memory matching your conditions
  2. Assign a random value RANDOM() to each row in the database
  3. Sort all the rows according to this random value
  4. Retain only the desired number of records from all sorted records

His solution is to pre-add randomness to each record, in an extra column. For it he uses a the Geometric Datatype POINT type. In Postgres he then uses the following query that orders the records by distance measured against a new random point.

SELECT * FROM repositories ORDER BY randomness <-> point(0.753,0.294) LIMIT 3;

~

In MySQL you also have a POINT class (ever since MySQL 5.7.6) that you can use. However I don’t really see how that would work there, as you’d need to calculate the distance for each record using a call to ST_Distance:

SET @randomx = RAND();
SET @randomy = RAND();
SELECT *, ST_Distance(POINT(@randomx, @randomy), randomness) AS distance FROM repositories ORDER BY distance DESC LIMIT 0,3;

💁‍♂️ Using EXPLAIN on the query above verifies it doesn’t use an index, and thus goes over all records.

What I do see working instead, is use of a single float value to hold pre-randomness:

-- Add column + index
ALTER TABLE `repositories` ADD `randomness` FLOAT(17,16) UNSIGNED NOT NULL AFTER `randomness`;
ALTER TABLE `repositories` ADD INDEX(`randomness`);

-- Update existing records. New records should have this number pre-generated before inserting
UPDATE `repositories` SET randomness = RAND() WHERE 1;

With that column in place, you could then do something like this:

SET @randomnumber = RAND(); -- This number would typically be generated by your PHP code, and then be injected as a query param
SELECT * FROM repositories WHERE randomness < @randomnumber ORDER BY randomness DESC 0,3;

Unlike the query using POINT(), this last query will leverage the index created on the randomness column 🙂

~

How to optimize ORDER BY RANDOM()

Via Freek

Creating random-but-stable effects with the CSS Paint API

One of the side-effects when drawing things with a Houdini Paint Worklet and relying on Math.random() in your code, is that your layout might be jumpy.

Check out my CSS Houdini Paint Worklet that draws colorful circles for example: whenever you resize the available space or change one of its properties — or some of the other CSS properties — the circles are being drawn, at random places with random colors.

Sometimes this side-effect is unwanted: say you have a Paint Worklet that draws some random lines underneath an element. If you were to animate the --line-width on hover you don’t want it to draw different lines, but you want to keep the same lines in place, and only adjust their width.

To solve around this, Jake Archibald shows us how to implement mulberry32, which has consistent output no matter how many times you call it:

Computers can’t really do random. Instead, they take some state, and do some hot maths all over it to create a number. Then, they modify that state so the next number seems unrelated to the previous ones. But the truth is they’re 100% related.

If you start with the same initial state, you’ll get the same sequence of random numbers. That’s what we want – something that looks random, but it’s 100% reproducible.

Go check out the demos Jake has built. As per usual these are top-notch, and help explain everything well.

CSS paint API: Being predictably random →