Stay in the index; fetch rows last (MySQL)
SoftwareAvoid MySQL early row lookups
Quote
a poorly thought out use of something like, group by or order by can trigger this problem where you end up fetching 100,000 rows. And then you actually only deliver 5000 of them.
Episode 178: Tepid Tub (episode page, opens in a new tab) 01:30:10 (play audio from this timestamp, opens in a new tab)
Speaker-labeled
Summary
Fixing a Podcast Index API bug where requests returned too few episodes, Dave explains MySQL 'early row lookups'. A poorly thought-out GROUP BY or ORDER BY can make MySQL fetch full rows from disk (say 100,000) before filtering down to the 5,000 you deliver. Keep the work in the in-memory index and fetch the actual row data at the last minute, finagling the query if needed.
Not in the audio. This technique is commonly called a 'deferred join' or 'late row lookup'. That name isn't used in the audio.