SELECT <cols> FROM profiles INNER JOIN (
SELECT <primary key cols> FROM profiles  WHERE x.age=29 ORDER BY rating LIMIT 100000, 10
) AS x USING(<primary key cols>);