Improving List Performance and Filter/Sort options

[F43nd1r]

Moving this out of commit comments:

Hey @F43nd1r - I was just browsing through code in acrarium with a goal of getting a version filter on the bug tab, and saw the beta release plus this native query code. Looks like filter-by-version is not possible on the bug query?

As a use case for AnkiDroid, that's my number one way to filter personally, we issue alphas every few days (with fixes of course...) and I want to filter for just the current release to see what it still valid.

Is that possible in some other way? Is there a way to add it to Acrarium? We're getting ready for our next beta cycle so this will be a focus for me, I could try to post a PR or at minimum test new releases etc, whatever seems most useful for collaboration if it's not already possible

Cheers :-)

— @mikehardy

I'm using this as a general discussion post about query optimization, including but not limited to filtering by version.

Some related discussion also happened in #324

[F43nd1r]

Okay, so here's the problem: Both VReport and VBug are aggregates over multiple tables, because the tables are modeled after logical entities. Sorting and/or filtering by a foreign property in mysql is very slow (most of the time a filesort over all reports is triggered).

My "fix" here was to only allow filtering and sorting on properties of the actual object - meaning columns in the main table being requested. A version code is not a property of bug, but of version, so we can't sort or filter by it without breaking performance.

For this specific case (and probably a lot of other specific cases) I could manually construct a query that performantly builds exactly the required result, but the issue is everything has to work together with all other filters or sort orders.

I have been thinking about this for a while, and the only solution I could come up with requires a paradigm shift - where tables are modeled after the view they are used for, not the entity they represent. That would result in quite a bit of data duplication though - meaning the read-effort is moved to a write-effort and consistency becomes a problem.

I'm not yet sure what exactly I'm aiming for
a) replace current tables
b) keep current tables but add a "cache" table for each view, which can be rebuilt in a pinch
c) don't build that cache based on the main DB at all, maybe in-memory h2 or something like that

I'm very much open to alternative suggestions

[mikehardy]

Hmm I see. I wonder about this -

A version code is not a property of bug, but of version, so we can't sort or filter by it without breaking performance.

Only for the app version, you've already done the computational work to pull the app version so it's available in some intermediate form at SQL level, and thus maybe where-able?

Only other thought is a precomputed view which solves consistency at cost of insert (i think)

A memory cache would kill us as I even wrote a wiki page on fitting in a small memory droplet in the cloud, we don't have ram to spare at current

Tough problem. At least for now on current stable release we can filter for app version on reports tab, so we do have a minimum viable solution and if one bug is repeated so often it makes the page hard to read we should fix it and release 😅

[F43nd1r]

Only for the app version, you've already done the computational work to pull the app version so it's available in some intermediate form at SQL level, and thus maybe where-able?

"app version" isn't really a clear term here, are you talking about the version name shown in the reports table? Or the latest version in the bug table?
Either way, yes filtering by these properties is technically possible, but prevent sql from using an index based sort resulting in bad performance.
For reports this is available in the current stable, as it's just a one to one join the performance impact is bearable in most cases.
For bugs this is currently a computed property which is terrible for performance when filtered, so it's not available at the moment.

Only other thought is a precomputed view which solves consistency at cost of insert (i think)

From what I could find this is usually called a "materialized view" and not natively supported by mysql, making it equivalent with my option b.

A memory cache would kill us as I even wrote a wiki page on fitting in a small memory droplet in the cloud, we don't have ram to spare at current

good to know, I'll take that into consideration.

[mikehardy]

"app version" isn't really a clear term here, are you talking about the version name shown in the reports table? Or the latest version in the bug table?

True it is a bit ambiguous. This is related to me sadly-duplicate #389 - what I want (and thus what I mean here) is the string "2.16alpha88" or whatever. That string shows up on the bugs web table (I think via the max sql on the collection of reports that make up the bug, then getting the name?) and then on the reports web table it's 1-to-1 I think(?) so it's just there.

And I think that gets to the heart of the performance concern (computed vs 1-to-1) which you addressed directly in your comment

I remembered materialized views from a long long time ago when I worked mostly with Oracle, I didn't realize MySQL did not have them.

I did see this as a "MySQL does not support materialized views" workaround: https://dba.stackexchange.com/a/157804 - aggregate table fed via insert triggers. 🤔

If it's not technically feasible (in a performance way that is also sensitive to memory) maybe it's just resolved -> wontfix and we use the reports table as our main workflow. Not all feature wishes can happen and that's okay.

[F43nd1r]

If it's not technically feasible (in a performance way that is also sensitive to memory) maybe it's just resolved -> wontfix and we use the reports table as our main workflow. Not all feature wishes can happen and that's okay.

That's not really a way forward, as I'd have to revert the performance improvements to keep your workflow alive. No this needs solving on a more fundamental level. (and it's not just about your use case, there's also several other features I had to remove like custom column filtering)

[mikehardy]

Ah - I haven't tried the beta release yet, I guess I was not personally clear that with the native query perf improvements you did the app version name went away on the reports tab. We definitely need someway to filter on that somehow. A general workflow being that ability to build a triage queue containing just crashes from the current alpha (even though previous alphas are of course streaming new reports in...).

Our work style is that we typically do general triage on current release, and only look at older ones on a per-installation basis when a user notes they have suffered a crash and provides their ACRA UUID