А при limit >= 40 запрос быстро отрабатывал (т.к. сначала используется индекс status, а потом результат сортируется filesort'ом), проблемы именно с limit [27..39] (тут индекс upped использовался для сортировки, а потом фильтр без индексов по всем строкам).
В общем по непонятным причинам при limit 36 стал опять использоваться индекс status, и запрос стал снова быстро выполняться. Были изменены 2 параметра (меняли не из-за этого запроса), которые, судя по explain (нет temp table), не должны были влиять:
max_heap_table_size = 320M
tmp_table_size = 640M
В общем по непонятным причинам при limit 36 стал опять использоваться индекс, и запрос стал снова быстро выполняться. Были изменены 2 параметра (меняли не из-за этого запроса), которые, судя по explain (нет temp table) не должны были влиять:
max_heap_table_size = 320M
tmp_table_size = 640M
sort_buffer_size - используется как вы и описали, т.е. во время выполнения алгоритма filesort, mysql как раз и выделяет буфер для пар <сортируемый атрибут, id>. У нас получается этот параметр имеет дефолтное значение 256K. Однако, судя по explain, filesort не используется в проблемном случае (limit 36), там сортировка идет на основании выстроенного индекса.
innodb_sort_buffer_size - определяет размер буфера, который используется при создании индекса вроде как.
innodb_buffer_pool_size = 96G # 128Gb total memory
innodb_sort_buffer_size = 1M # default value
Но innodb_sort_buffer_size, как я понимаю, тут влиять не должен?
На самом деле я не понимаю почему в случае с `limit 36` mysql не использует индекс `status` (т.е. я так понимаю он сначала сортирует, а потом делает фильтр - тогда почему explain выводит такое малое значение rows?), а с `limit 40` - использует сначала фильтр по индексу, а потом результат сортирует. В случае с `limit <=26` он тоже сначала сортирует, а потом делает фильтр (но 26 строк он быстро находит, т.к. они относительно вверху, и не идет дальше)
