Good day. There is a request:

select t1.id, t1.f1, t1.f2, t1.f3, t1.f4, t2.date, t3.f5, t2.f6, t2.f7 FROM ( select date, id, f7, f6, f8 FROM t2 WHERE t2.date >= ? and t2.date <= ? ) as t2 INNER JOIN t1 on t2.id = t1.f9 LEFT JOIN t3 on t2.f8=t3.id ORDER BY t2.date LIMIT 5499945,99999

The problem is that for large LIMIT OFFSET values, the request will be executed a terribly long time. What can be done as you can optimize.?

  • to remove the attached request? Also see EXPLAIN (maybe there are not enough indices) - KoVadim
  • If you remove the subquery, processing time increases by 50% - Devlog
  • And what happens with id in time. Is it possible to replace limit with the operation > < with id. This could give a big boost. In fact, you choose 5499945 extra records, sort them and then just throw them away. If id grew in time and you would have saved the lower bar where necessary id could not choose these records at all - Mike
  • Similarly, if the date contains a time, it would be possible to store somewhere the upper time bar and a small offset relative to the beginning of the given second in the date field. and fix the date interval condition - Mike
  • Considered this option. But unfortunately the sample should be sorted by date (necessary condition) and id can be backdated (ie, a larger id for a smaller date) - Devlog

1 answer 1

Filesort, yes on such volumes (judging by the LIMIT parameters), well, I can’t do it quickly. And taking into account that the sorting goes across the field of the subquery, i.e. on a non-indexed data set, I don’t see the potential for optimizing this query in principle.

I propose to consider the option of transforming the request into the procedure. In it, make a sample of the subquery in TEMPORARY TABLE ENGINE = Memory, index it, and only then get the final sample. The greater the number of records the subquery splits after linking with other tables, the greater the chance of having a profit ... yes, the subquery itself (selection on a temporary basis) should also be limited, or in any case, try to do it.