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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
