Представим, что у нас есть таблица пользователей (users) или заказов (orders) на десятки миллионов записей и больше. И у нас стоит задача их перебора в любом контексте.
Я представляю себе это в виде цикла, в котором порционно забираем разумное количество записей, что-то вроде:
select * from users order by id limit 1000 offset 0;
Такой запрос выполняется очень быстро, моментально.
Рано или поздно мы приходим к offset, например 40 млн:
select * from users order by id limit 1000 offset 40000000;
И тут запрос может выполняться несколько минут.
Происходит это из-за того, что mysql не может гарантировать, что все элементы будут на своих порядковых местах, ведь может так статься, что элемент 40 000 099 был ранее удален и алгоритмам приходится сканировать все 40 млн элементов для получения выборки.
Решением этой проблемы может стать видоизменение запроса из limit-offset на where-limit:
select * from users where id > 40000000 order by id limit 1000;
Выполняется моментально!
И даже, если не все элементы гарантированно присутствуют в таблице, айдишник для where легко «вытащить» из предыдущего запроса в цепочке.