@Dominique Devienne > SQLite supports function-based indexes, but unfortunately if does not support "function-based columns".
Far fetched maybe, but could a virtual table or table-valued functions be used to provide that? ie. use the virtual table to pass data directly to an index, and then expose the data stored in the index as a column. (I am not accomplished enough about sqlite indexes to know how far fetched the above would be) @Rowan Worth > What if you could create a "lite" index, which stores just the rowids in a particular order and > refers back to the table for the rest of the column data? As I have millions of rows, and data could get inserted anywhere in that index (given the values are essentially random), maintaining such an index would not be light work. @Simon Slavin > A later post from OP suggests that the critical point here really is filespace, not search time, Yes, this is because in the rest of the schema I am always using the key (integer), so the value -> key lookup only happens in API parameters, never in joins or other internal processing. The key -> value lookups need to be faster as they happen in SQL outputs, but at worst each query will be doing thousandths of those. So a little loss of performance there could be acceptable there as well. Eric On Mon, Jul 30, 2018 at 11:40 AM, Simon Slavin <slav...@bigfraud.org> wrote: > On 30 Jul 2018, at 10:25am, Dominique Devienne <ddevie...@gmail.com> > wrote: > > > The former allows you to get what you want, but as you wrote, you must > > rewrite your queries. The latter, > > if supported, would allow to move the "function definition" to the > column, > > and index the vcolumn directly. > > It's the usual speed vs. filespace issue. And as usual it comes down to > > how speed-critical searches are > how often you do searches, and > relative frequency of making changes vs. searching > > A later post from OP suggests that the critical point here really is > filespace, not search time, so my previous recommendation is perhaps not > appropriate for this situation. > > Simon. > _______________________________________________ > 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
