Как вывести списки с постраничной навигацией для базы в 2 млн записей?
Это каноническая задача на самом деле.
Дело в том, что для обработки offset 400 000 - sql сервер должен фактически найти 400 000 записей удовлетворяющих условию, отбросить их и дальше уже вывести результат.
/tmp при этом переполняться по идее не должен, могу предположить что серверу тупо не хватает памяти.
Покрутите mysqltuner какой-нибудь.
Нормальных решений проблемы нет, есть обходные пути:
1) Вводите какой-то неразрывный индекс удовлетворяющий выборке, вместо limit делаете where tmp_id between 400000 and 400100 - в вашем случае вероятно не подойдет.
2) Придумываете какой-то альтернативный способ пагинации опять же без offset - например перебор дат записей или еще что-то.
3) Задаетесь глобальным вопросом нафига вообще пагинация на 400 000 записей и отказываетесь от неё :-D
Очевидно что пользователь такое количество записей не воспримет никогда, так что пусть уточняет запрос.
Попробуйте подойти иным путем к задаче: зачем нужны страницы у такой выборки?
Сделайте интерфейс, выведите в него хотя бы 200 записей и попробуйте поработать. Вы быстро поймете что листать страницы неудобно и не нужно.