> On Mar 26, 2018, at 11:46 PM, rene <[email protected]> wrote: > > Question 1: Is it possible to create an index on "name" and "id" inside > sqlite
Not really, because SQLite indexes only support values with a 1::1 relationship to table rows; in other words, the index can have only one entry per table row. But your rows can have any number of name/id pairs. I brought this up two weeks ago on this list in a thread titled "Indexing multiple values per row”; Dr Hipp gave an interesting suggestion: >> Perhaps use FTS4 or FTS5 with a custom tokenizer that understands JSON? This has promise, since the FTS module does support indexing multiple values (words) per table row. I haven’t tried it yet, though. The approach I’ve used in the past is to create a derived table containing every name/value pair from the main table, and indexing that. The main problem with that is you have to keep that table up to date as changes are made to the main table. If I were going to re-implement this today, I’d probably use triggers for that. —Jens _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
