search

celzero / rethink-app

DNS over HTTPS / DNS over Tor / DNSCrypt client, WireGuard proxifier, firewall, and connection tracker for Android.
https://rethinkfirewall.com/
Apache License 2.0
2.85k stars 145 forks source link

Revisit Room for possible index creation. #330

Closed hussainmohd-a closed 1 year ago

hussainmohd-a commented 3 years ago

Indexes aren't free though, and there are more than one type, each suited to different kinds of query patterns and space complexity: https://archive.is/j1nTl

https://github.com/celzero/rethink-app/pull/282#discussion_r663547351_

ignoramous commented 3 years ago

See also:

AppSearch: https://developer.android.com/guide/topics/search/appsearch (Android 12+).

Squeezing performance from SQLite with Room: https://archive.is/mN83U

Deep dive into Room persistence library: https://archive.is/XaOEy

ignoramous commented 1 year ago

Indices aren't really required (since primary keys are approp set) except for indices required for ipAddress and dnsQuery Stats screen: https://github.com/celzero/rethink-app/commit/019eacb0f7e45d448e845a6fadb85d67655fe831

As for app-search, that's a different issue and tracked #743

ignoramous commented 1 year ago

cov index for order by queries also resulted in slow performance for tables (network logs / dns logs) with > 100K entries: https://stackoverflow.com/a/50776662

a potential soln is to use primary key (auto-incrementing ID) for sorting (order by) or add an index on the sorted (usually, timeStamp) field.