google / android-fhir

The Android FHIR SDK is a set of Kotlin libraries for building offline-capable, mobile-first healthcare applications using the HL7® FHIR® standard on Android.
https://google.github.io/android-fhir/
Apache License 2.0
494 stars 295 forks source link

Update db covering indexes to have columns likely to be unique leftmost #2712

Open LZRS opened 2 weeks ago

LZRS commented 2 weeks ago

based on https://www.sqlite.org/queryplanner.html#_multi_column_indices and https://www.sqlite.org/optoverview.html

IMPORTANT: All PRs must be linked to an issue (except for extremely trivial and straightforward changes).

Fixes #[issue number]

Description Refactor db covering indexes to have their leftmost columns to be those most likely to be unique (also mostly likely to be used) Based on https://www.sqlite.org/queryplanner.html#_multi_column_indices

The left-most column is the primary key used for ordering the rows in the index. The second column is used to break ties in the left-most column. If there were a third column, it would be used to break ties for the first two columns. And so forth for all columns in the index.

And https://www.sqlite.org/optoverview.html

It is not necessary for every column of an index to appear in a WHERE clause term in order for that index to be used. However, there cannot be gaps in the columns of the index that are used

Alternative(s) considered Have you considered any alternatives? And if so, why have you chosen the approach in this PR?

Type Choose one: (Bug fix | Feature | Documentation | Testing | Code health | Builds | Releases | Other)

Screenshots (if applicable)

Checklist

LZRS commented 3 days ago

Hi @LZRS thanks for this draft PR - this is really important!

Some comments on the draft PR: it would be much easier to review smaller changes because this touches so many tables - maybe we can have 1 pr to change the indices and another one to change the queries.

secondly, can you use query planner in android studio to check if the indices are being hit? and do you have actual numbers with regards to time taken for these queries?

Okay, I will do that and share some of the related query plans