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

Performance-Testing the F1 websites

Jake Archibald has published a nice series in which he has been been performance-testing all F1 sites. Not only does he dig into waterfall charts, he also points out a few simple things that could be applied in order to improve overall loading performance.

As a bonus he also tested the Google I/O site, which uses an SPA approach. After 9 seconds of showing nothing, eventually a spinner is shown. At the 26 second mark (!) all data was prepared and done, and the page got rendered. As Jake puts it:

Imagine you went to a restaurant, took a seat, and 20 minutes later you still haven’t been given a menu. You ask where it is, and you’re told “oh, we’re currently cooking you everything you might possibly ask for. Then we’ll give you the menu, you’ll pick something, and we’ll be able to give you it instantly, because it’ll all be ready”. This sounds like a silly way to do things, but it’s the strategy the I/O site is using.

I really recommend you to read all posts thoroughly, as there’s a ton to learn from all this!

Who has the fastest F1 website in 2021? Part 1 →
Performance-testing the Google I/O site →

Too Many SVGs Clogging Up Your Markup? Try use

Good reminder by Georgi Nikoloff to have one (visually hidden) SVG that contains several layers, which you can then include further down in your code.

SVG has a <defs> tag that lets us declare something like our graph footer just once and then simply reference it — using <use> — from anywhere on the page to render it as many times as we want.

That way you end up with less DOM nodes, less bytes transferred, and less memory consumed.

Too Many SVGs Clogging Up Your Markup? Try use

Debugging Layout Shifts

Over at web.dev, Katie Hempenius learns us how to identify and fix layout shifts using the Layout Instability API and the DevTools.

What I take away from this is that you can easily spot them using DevTools: In the Rendering Panel you can enable an option to highlight areas of Layout Shift:

To enable Layout Shift Regions in DevTools, go to Settings → More Tools → Rendering → Layout Shift Regions then refresh the page that you wish to debug. Areas of layout shift will be briefly highlighted in purple.

Debugging layout shifts →

JavaScript performance beyond bundle size

Nolan Lawson, on how we might focus too much on JavaScript bundle size:

Performance is a multi-faceted thing. It would be great if we could reduce it down to a single metric such as bundle size, but if you really want to cover all the bases, there are a lot of different angles to consider.

He digs into other factors that have an influence:

  • Parse/compile time
  • Execution time
  • Power usage
  • Memory usage
  • Disk usage

JavaScript performance beyond bundle size →

Before You React.memo()

Dan Abramov shares 2 techniques to try before resorting to optimize with React.memo()

In this post, I want to share two different techniques. They’re surprisingly basic, which is why people rarely realize they improve rendering performance.

These techniques are complementary to what you already know! They don’t replace memo or useMemo, but they’re often good to try first.

Covered Solutions:

  1. Move State Down
  2. Lift Content Up

Before You memo() →

Debugging layout repaint issues triggered by CSS Transition

Dzhavat ran into an interesting performance issue where practically his whole site would repaint when a transition in the header was triggered.

The element being animated is a span wrapping some text placed inside an h1. The h1 itself is in the upper left corner on the page and contains my name. Initially, only the letter “D” is shows. The remaining part fades-in on hover.

I was quite surprised to see the whole page flashing green given the transition was scoped to a very isolated element. I didn’t really see any connection between animating a span and causing repaint on the whole page.

The culprit: Stacking Contexts

Debugging layout repaint issues triggered by CSS Transition →

How to avoid layout shifts caused by web fonts

Simon Hearne, who has been keeping a closer eye on unexpected layout shifts ever since Core Web Vitals came into play:

One common cause of layout shift is surprisingly difficult to resolve though: flashes of unstyled text (FOUT). In this post we will explore the surprisingly complex world of text rendering on the web and some techniques to remove FOUT while not incurring a CLS penalty.

How to avoid layout shifts caused by web fonts →

How We Improved SmashingMag Performance

Interesting real-world case-study over at Smashing Magazine:

In this article, we’ll take a close look at some of the changes we made on this very site — running on JAMStack with React — to optimize the web performance and improve the Core Web Vitals metrics. With some of the mistakes we’ve made, and some of the unexpected changes that helped boost all the metrics across the board.

Be sure to check out the linked diagnostics.css by Tim Kaldec. It can be used to indicate clear (or potential) problems with the markup.

How We Improved SmashingMag Performance →

content-visiblity: auto; vs. jumpy scrollbars, a solution

As warned in content-visibility: the new CSS property that boosts your rendering performance you need to be careful with applying content-visibility: auto; on each and every element as the scrollbar might get jumpy.

This is because elements will be rendered as they scroll into the viewport and will be hidden as they scroll out of the viewport, thereby affecting the height of the rendered page, and thus also affecting the scrollbar.

ℹ️ Apart from a jumpy scrollbar it can also negatively affect accessibility when you include headings and landmark elements inside of regions styled with content-visibility: auto;. See Content-visibility and Accessible Semantics for details.

Now, thanks to infinite scroll we are — or at least I am — kind of used to the thumb part of the scrollbar shrinking and jumping back up a bit on the scrollbar track as you scroll down. What we’re not used to is the thumb part jump forwards on the scroll track as you scroll down. This is because elements that slide out of the viewport will no long be rendered — as that’s what content-visibility: auto; does — and the scrollbar is (by default) only calculated against the rendered elements.


Elements can become non-rendered elements as they scroll out of the viewport,
thanks to content-visibility: auto; doing its thing.

To cater for this jumpy behavior you should use contain-intrinsic-size so space for an element is reserved when it’s not being rendered. However, it is not always possible to know a box its dimensions in advance. Looking for a way to automatically reserve space for previously rendered elements, Alex Russel created a little script for it.

One challenge with naive application of content-visibility, though, is the way that it removes elements from the rendered tree once they leave the viewport — particularly as you scroll downward. If the scroll position depends on elements above the currently viewable content “accordion scrollbars” can dance gleefully as content-visibility: auto does its thing.

In a first version of the script he applied content-visibility: visible on each element from the moment it had appeared on screen. To detect this an IntersectionObserver is used. While this does prevent the scrollbar thumb from jumping forwards as you scroll down, it will make the page slow again as that content remains rendered (even though it’s off-screen).

A second version of the script takes a different approach and calculates the contain-intrinsic-size to apply based on the element’s dimensions. That way elements that passed by once now have a correct contain-intrinsic-size set, and can safely be hidden again as content-visibility: auto does its job.

let spaced = new WeakMap();
let reserveSpace = (el, rect) => {
    let old = spaced.get(el);
    // Set intrinsic size to prevent jumping.
    if (!old || rectNotEQ(old, rect)) {
        spaced.set(el, rect);
        el.attributeStyleMap.set(
        "contain-intrinsic-size",
        `${rect.width}px ${rect.height}px`
        );
    }
};

Additionally he also added a ResizeObserver to cater for resize events.

Resize-Resilient `content-visiblity` Fixes →

🤔 Clever script indeed, yet I cannot help but think: this should be possible without the needs for this extra script. What if a value like contain-intrinsic-size: auto; would be allowed, and do exactly as the script Alex built does?

UPDATE 2020.12.21: I’ve created an issue on GitHub that proposes contain-intrinsic-size: auto;. Let’s see where this goes …

UPDATE 2021.01.13: Looks like support for contain-intrinsic-size: auto; will land in the spec!