I have the same problem but luckily O(n) performance is fast enough for me right now.
It seems to me that the only real option is to maintain a derived table, the table could even be R*TREE to allow range queries. Triggers seem to be the accepted way to derive tables, but I'm a little afraid of them not always firing (sqlite versions, configuration glitches, whatever reason), which would cause the table to be incorrect. On Fri, Mar 16, 2018 at 9:24 AM Hick Gunter <h...@scigames.at> wrote: > So in your application you don't care about when a temperature was > measured? Creating a table temps (patient_id, timestamp, temperature) with > an index on (timestamp,temperature) would yield a fast way to access > patients with elevated temperatures within a time frame. > > Other than that, using triggers is probably the easiest way to go. > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Jens Alfke > Gesendet: Donnerstag, 15. März 2018 18:34 > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Betreff: [EXTERNAL] [sqlite] Indexing multiple values per row > > I'm wondering what the best way is to efficiently search for data values > that can appear multiple times in a table row. SQLite indexes, even > expression indexes, don't directly work for this because they obviously > only index one value per row. Traditional relational-database design says > to normalize the schema by storing the multiple values in separate rows, > but what if the data to be indexed is JSON and you need to leave it in that > form? > > For example, let's say I have a table like > patient_id: 12345 > temps: "[98.6, 99.1, 101.3, 100.0, 98.9]" > and I want to run queries on temperature data, like 'patients who've had a > temperature above 101'. And I need better than O(n) performance. > > In the past my project used map/reduce to support this, essentially > implementing its own index system on top of SQLite tables. In this case it > would create a table (patient_id integer primary key, temp number) and > populate it by scanning the patient table. This can obviously be indexed > easily, but updating the table before a query when the source table has > changed is a pain in the butt. > > I believe a SQL "materialized view" would do what I want, but SQLite > doesn't support those; its views seem to be just syntactic sugar or macros > around SELECT queries. > > Other than that, my best idea so far is to simplify the map/reduce > updating by adding triggers on the source table that will add & remove rows > from the index table. > > Is there any other clever way I could do this? (I've been using SQLite for > 14 years and I still keep learning about more clever things it can do…) > > —Jens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse > 2 <https://maps.google.com/?q=Klitschgasse+2&entry=gmail&source=g>-4, > A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 > <+43%201%2080100> - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
