Re: [sqlite] Index on expressions
On Wed, Oct 22, 2014 at 3:42 PM, Philippe Riand wrote: > Hello, > If there any plan to get this in? (see: > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-September/055065.html > ) You'll notice that the SQLite3 developers don't often discuss what features they will be adding (nor when), unless they are ready to make a public commitment. That's how it looks to me anyways. That said, Richard Hipp did not reject your request, and qualified the "SQLite3 does not support that" reply with "(yet)". Take that for what you will, though it's clearly NOT a rejection. We'll find out eventually. (You can always watch the public parts of the Fossil repo for SQLite3, but you shouldn't count on anything you see there being committed to.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index on expressions
Hello, If there any plan to get this in? (see: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-September/055065.html ) Note that this seems implemented by several databases now, and this is a bit different from virtual columns, which is what the previous discussion ended with. Virtual columns force a table alteration (adding the columns) why indexes on expressions eventually speed up the search on conditions like f(x)='value', if the index exists. You add the index only if it makes sense from a performance standpoint, and it is not required. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On Thu, Sep 4, 2014 at 3:59 AM, Dominique Devienne wrote: > On Thu, Sep 4, 2014 at 2:04 AM, Nico Williams wrote: > >> [...] but there's a gotcha: SELECT * on a table >> source with computed columns might result in much more work being done >> than the user might have expected. > > If that's a real concern, you can always use the existing HIDDEN keyword to > remove that virtual/computed column from the select-star. --DD Ah! Good point. Getting computed columns as part of the process of getting indexing on expressions would be very, very good. It would allow having computed columns in PRIMARY KEY and UNIQUE table constraints, for example. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
Dominique Devienne wrote: (double-click on new version) SQLite version 3.8.5 2014-06-04 14:06:34 (terminal) SQLite version 3.7.13 2012-07-17 17:46:21 This means you downloaded the new version in a directory not in your $PATH Add it to your path: set PATH=: $PATH If you do this in your startupscript you always get the latest Another option (not recommended!) is to overwrite the systemversion with sudo cp /sqlite3 /usr/bin/sqlite3 Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse, Klaas `Z4us` V - OrcID -0001-7190-2544 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On Thu, Sep 4, 2014 at 2:04 AM, Nico Williams wrote: > [...] but there's a gotcha: SELECT * on a table > source with computed columns might result in much more work being done > than the user might have expected. > If that's a real concern, you can always use the existing HIDDEN keyword to remove that virtual/computed column from the select-star. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
Also, of course, MERGE is very convenient, syntactically and semantically. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On Wed, Sep 3, 2014 at 6:16 PM, Simon Slavin wrote: > I'm used to calling them 'computed columns' but yes, they should be > relatively easy to implement, as long as users accept a bunch of restructions > on what they can refer to, roughly equivalent to the restrictions on what can > be used in CHECK constraints. I had hoped for them in SQLite4. > > One alternative is to use VIEWs but, as has already been said in this thread, > one can't index a VIEW. And another alternative is to use TRIGGERs and that > works fine, but it's bulky and annoying to implement. Triggers are very expensive. IMO an index on expressions would be extremely valuable. Computed columns go hand in hand with this, in my mind, as no extended CREATE INDEX syntax is needed then, but there's a gotcha: SELECT * on a table source with computed columns might result in much more work being done than the user might have expected. > The MERGE command would, I'm guessing, be far harder to implement because it > does such different things depending on what data exists in the tables. Not > only would writing the code be difficult but also thinking up the numerous > tricky things that would need to go into the test suite. A MERGE basically does something like three statements, an INSERT ... WHERE ; and UPDATE ... WHERE , and a DELETE ... WHERE ;. I do this sort of thing all the time. Expanding a MERGE into something that shouldn't be hard. The real value of MERGE is that the underlying query that drives this need only be evaluated once. The SQLite3 VM, I think, can handle this, so it's mostly a matter of compiler code (yeah, I know, famous last words). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On 3 Sep 2014, at 8:21pm, John McKown wrote: > Those are _both_ nice features. I don't know for sure, but somehow it > seems that virtual columns would be easier to implement. I'm used to calling them 'computed columns' but yes, they should be relatively easy to implement, as long as users accept a bunch of restructions on what they can refer to, roughly equivalent to the restrictions on what can be used in CHECK constraints. I had hoped for them in SQLite4. One alternative is to use VIEWs but, as has already been said in this thread, one can't index a VIEW. And another alternative is to use TRIGGERs and that works fine, but it's bulky and annoying to implement. The MERGE command would, I'm guessing, be far harder to implement because it does such different things depending on what data exists in the tables. Not only would writing the code be difficult but also thinking up the numerous tricky things that would need to go into the test suite. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On Wed, Sep 3, 2014 at 1:35 PM, Petite Abeille wrote: > > On Sep 3, 2014, at 3:01 PM, Dominique Devienne wrote: > >> Asked differently, if adding this support, could this be done by adding >> virtual / computed columns to tables, and indexing those columns? > > Ohohohoho… virtual columns [1][2]…. yes… shinny! :) > > Now that would be rather cool. > > On the other hand, if one had to choose, I would rather see a MERGE > statement, than some funky virtual columns. > > While virtual columns are handy at time, they are a bit exotic, all things > being equal. > > On the other hand, MERGE is a must have. No amount of creative select + > insert + update concoctions can begin to compensate for its absence in > SQLite. A huge gap altogether. > > > [1] http://en.wikipedia.org/wiki/Virtual_column > [2] http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php > Those are _both_ nice features. I don't know for sure, but somehow it seems that virtual columns would be easier to implement. OTOH, I really like what I've just read about the MERGE statement. I only use SQLite and PostgreSQL. So I had never heard of MERGE before, but the Oracle documentation on them, and the example, look interesting. -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On Sep 3, 2014, at 3:01 PM, Dominique Devienne wrote: > Asked differently, if adding this support, could this be done by adding > virtual / computed columns to tables, and indexing those columns? Ohohohoho… virtual columns [1][2]…. yes… shinny! :) Now that would be rather cool. On the other hand, if one had to choose, I would rather see a MERGE statement, than some funky virtual columns. While virtual columns are handy at time, they are a bit exotic, all things being equal. On the other hand, MERGE is a must have. No amount of creative select + insert + update concoctions can begin to compensate for its absence in SQLite. A huge gap altogether. [1] http://en.wikipedia.org/wiki/Virtual_column [2] http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
Use a trigger to populate your index column. http://www.sqlite.org/lang_createtrigger.html On Wed, Sep 3, 2014 at 9:01 AM, Dominique Devienne wrote: > On Wed, Sep 3, 2014 at 2:52 PM, Richard Hipp wrote: > > > SQLite does not (yet) support indexes on expressions. > > > This begs the question: Are there plans, possibly ongoing, to add this > support? Any timeframe? > > Asked differently, if adding this support, could this be done by adding > virtual / computed columns to tables, and indexing those columns? > > You can emulate virtual columns with views of course, but then you have a > table and view, necessarily named differently, and of course you cannot > index views, while some DBMS allow indexing virtual / computed columns. > --DD > > Fantasy SQL: > create table t (id number primary key, c1, c2, ... ); > alter table t add column c99 as (c1 + c2); > create index idx_t_c99 on t.c99; > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
On Wed, Sep 3, 2014 at 2:52 PM, Richard Hipp wrote: > SQLite does not (yet) support indexes on expressions. This begs the question: Are there plans, possibly ongoing, to add this support? Any timeframe? Asked differently, if adding this support, could this be done by adding virtual / computed columns to tables, and indexing those columns? You can emulate virtual columns with views of course, but then you have a table and view, necessarily named differently, and of course you cannot index views, while some DBMS allow indexing virtual / computed columns. --DD Fantasy SQL: create table t (id number primary key, c1, c2, ... ); alter table t add column c99 as (c1 + c2); create index idx_t_c99 on t.c99; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index on expressions
SQLite does not (yet) support indexes on expressions. On Tue, Sep 2, 2014 at 9:06 PM, Philippe Riand wrote: > Is there a way to create indexes based on expressions instead of simple > columns (see: > http://www.postgresql.org/docs/8.1/static/indexes-expressional.html)? The > idea is to have some custom functions that extract data from a JSON column > and allow a fast query based on an index. This is the strategy used by > POSTGRES and DB2, for example. > I know that we can create a table and do a manual field extraction, but > this is not transparent from a query standpoint, and forces a manual re > computation of all rows when a new index is desired. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index on expressions
Is there a way to create indexes based on expressions instead of simple columns (see: http://www.postgresql.org/docs/8.1/static/indexes-expressional.html)? The idea is to have some custom functions that extract data from a JSON column and allow a fast query based on an index. This is the strategy used by POSTGRES and DB2, for example. I know that we can create a table and do a manual field extraction, but this is not transparent from a query standpoint, and forces a manual re computation of all rows when a new index is desired. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users