Memory error in API Live.getLastVisitsDetails when filter_offset is large

[mattab]

The goal of this issue is to investigate and solve a Live API request performance issue, when the request contains a high filter_offset paramater value.

The system runs out of memory, when requesting the following Query:

index.php?module=API&method=Live.getLastVisitsDetails&idSite=4&period=day&date=yesterday&format=xml&token_auth=&filter_limit=1&filter_offset=60000

This API request will return only one row. Instead, Piwik API currently errors out with a memory error (1G exhausted) as it seems to iterate over rows in memory. Instead we should aim to make this API call blazing fast and memory efficient, as users expect it to be.

[tsteur]

I had a quick look and noticed it is not easy to fix. It takes probably 1.5 days to find a proper working solution.

Problem 1: If we apply filter_limit and filter_offset in getLastVisitDetails then the same would be applied again during GenericFilter and in the end it would not return anything. That's why we currently fetch filter_limit + filter_offset so the GenericFilter can apply the limit.
Problem 2: Adding a LIMIT 5, $offset is not trivial. While the API supports already to specify a limit it does not support to specify a $offset. Changing this results in touching many classes as it goes down till Segments etc.