How to display lists with pagination for a database of 2 million records?

Question

MySQL

Complex request overflows /tmp in linux
Works on the first pages without problems, hangs up on pages from 200 and above.
The request is stupid, what suggestions for optimization?
There are indexes, there are also composite ones (already) :)
There, sorting is very complicated:

SELECT (запчасти) FROM (таблица) WHERE category_id IN (%d....%d) OR category_id IS NULL ORDER BY category_id = main_category_id DESC, ISNULL(category_id), ISNULL(name), partnumber, name;

Because of two million records (some say that for a DBMS two million it should be something inconspicuous and instantaneous) - on page 200, when OFFSET gets close to 400 thousand - /tmp is hung up.
Suggestions?

Answer 1

This is actually a canonical issue.
The fact is that in order to process offset 400,000 - sql server must actually find 400,000 records that satisfy the condition, discard them, and then display the result.
/tmp should not overflow at the same time, I can assume that the server is stupidly short of memory.
Twist mysqltuner some.
There are no normal solutions to the problem, there are workarounds:
1) Enter some kind of non-breaking index that satisfies the selection, do it instead of limit where tmp_id between 400000 and 400100- in your case it probably won't work.
2) Come up with some alternative way of pagination, again without offset - for example, enumeration of post dates or something else.
3) You ask yourself the global question what for in general pagination for 400,000 records and refuse it :-D
Obviously, the user will never accept such a number of records, so let him clarify the request.

Answer 2

If id is an auto-increment, then you can navigate by it by dropping offset.

Answer 3

Try to approach the problem in a different way: why do we need pages for such a selection?
Make an interface, display at least 200 entries in it and try to work. You will quickly realize that flipping pages is inconvenient and unnecessary.