Is it MySQLs fault that wordpress asks it to do something that's hard?
LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of MySQL, you need to know how it works and write your queries accordingly. Sometimes that means get all the data unsorted from MySQL and sort it yourself, which is unfortunate, but frontend cpu + memory is cheaper than database I/O to make a temp table and sort it
LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of MySQL, you need to know how it works and write your queries accordingly. Sometimes that means get all the data unsorted from MySQL and sort it yourself, which is unfortunate, but frontend cpu + memory is cheaper than database I/O to make a temp table and sort it