MySQL optimisations for high performance

Deferred join

• useful for deep paging (loading rows from highest offset)
• using a covering index to retrieve just the primary key columns of the rows you’ll eventually retrieve
• the difference against the normal SELECT is that we don't load the additional (=non-covering index) data before sorting 
• example on Stackoverflow

Example:

SELECT firstName, children FROM people
JOIN (
    SELECT id FROM people
    WHERE children > 0
    ORDER BY children DESC
    LIMIT 0, 10000
) s
ON people.id = s.id
ORDER BY children DESC